Calculate Time Elapsed but exclude Non-Working Hours (1 Viewer)

Solved:Calculate Time Elapsed but exclude Non-Working Hours

Good Day!

I have a start date and time (06/16/2016 12:52:01 AM) and an end date and time (06/23/2016 12:52:01 PM), I need to calculate the time elapsed between this two but it should only include the working hours which is from 8:00 AM until 5:00 PM of the day. The time 5:01 PM until 7:59 AM should be excluded together with Weekends and holidays. The result that i'm hoping should be in this format hh:nn:ss.

Can someone help me out with my problem or point me to the right direction please. Any help is greatly appreciated.

Thank you
Hudas

This will require you to write a VBA function, I think, because the SQL for this would become nightmarish.

Before I re-invent wheels for you, I strongly recommend you search the forum for the subject "Exclude non-working hours" and the subject "Working hours". You are not the first person (or 2nd or even 52nd person) to ask a question of this type.

The search function is near the top of the page, two options from the right of that menu ribbon. It will probably give you some help. If not, come back and ask some more direct questions if you don't understand it. But seriously, this question HAS been answered on THIS forum many times.

Thank you The Doc Man. Indeed I was able to find the solution when I search for "Exclude non-working hours". Here it is http://www.access-programmers.co.uk...?t=159163&highlight=Exclude+non-working+hours
Thank you!

i want to know about calculation hrs & minutes between two dates & time,

how can i create quiries in access to calculate hours and minutes between to dates and time any one halp me.

Sweety, you DO know you are responding to a three-month old thread? You would do better to start a new one. Consider this the gentlest of "tweaks" to advise you of the way this forum works. Really, last time I looked there was no charge for starting a new thread of your very own!

OK, having now said that, here is a solution of sorts:

If both of your critical dates are in DATE variables OR are in tables as DATE fields OR some reasonable combination thereof, then they are stored in what is called a typecast (or sometimes just a cast) of DOUBLE format. They are equal to the time as days and day-fractions since the system reference date, which is either 31-Dec-1899 or 1-Jan-1900, I forget exactly which - and it doesn't matter which.

The difference between two DATE variables is an elapsed time in days and day fractions. I'm betting that you don't need hours and minutes separately, only for display purposes. So...


The 3 "h" characters in a row represent an elapsed-time format conversion. The 2 "n" characters are formatting specs for minutes (because "m" is used for months and would bollix up the works badly.)

i would advice not to wake the dead threads.
so you won't get nervous when somebody answers you back.