How can I calculate time durations for only business hours?

[Infiltrator64]

I need some help with a query that will determine the duration of time between two column values.

I need to have the calculation determine if the first column that will be compared to the second column is after 5:00PM on a weekday or if the date/time is on a weekend.

If it is after 5 on a weekday I need is to use 8:00AM the following day as the comparison.

If it is on a weekend I need it to use 8:00AM the following Monday as the comparisons.

I am doing this now in excel with the formula below however I’m not sure how to do this in Access.

=IF(OR(E3="",F3=""),"",(NETWORKDAYS(IF(E3>F3,D3,E3),F3)-1)*(3/8)+IF(WEEKDAY(F3,2)>5,17/24,MEDIAN(MOD(F3,1),17/24,1/3))-IF(WEEKDAY(IF(E3>F3,D3,E3),2)>5,1/3,MEDIAN(MOD(IF(E3>F3,D3,E3),1),17/24,1/3)))

 

[Andrew Fergus]

Re: How can I calculate time durations for only business hou

Hi

In response to this part :

If it is after 5 on a weekday I need is to use 8:00AM the following day as the comparison.

If it is on a weekend I need it to use 8:00AM the following Monday as the comparisons.

the following expression in a query will convert the first field (assuming the date and time are in the same field, I used the field name 'DateTime' in my test) into the date and time you asked for.

You can then use this value in a DateDiff function with the 2nd date to determine the difference.

There might be an easier (and shorter) way of doing this but my testing showed this method to work :

NewStart : IIf(Weekday([DateTime])=1 Or Weekday([DateTime])=7 Or (Weekday([DateTime])=6 And DatePart("h",[DateTime])>16), DateAdd("d",IIf(Weekday([DateTime])=1, 1, 9-Weekday([DateTime])), DateSerial(Year([DateTime]), Month([DateTime]), Day([DateTime])) & " " & TimeSerial(8,0,0)), IIf(DatePart("h", [DateTime])<8, DateSerial(Year([DateTime]), Month([DateTime]), Day([DateTime])) & " " & TimeSerial(8,0,0), IIf(DatePart("h",[DateTime])>16, DateAdd("d",1, DateSerial(Year([DateTime]), Month([DateTime]), Day([DateTime])) & " " & TimeSerial(8,0,0)), [DateTime])))

HTH, Andrew

P.S. If you edit your post and insert a few spaces into your formula then you may make both posts more readable! The screen won't appear so wide.

 

[Infiltrator64]

Perfect! it works.

Thanks Andrew this works perfectly.

 

[yau828]

Andrew, I hope you still available to help or any sifoo out there.

The syntax is simply amazing! but I would like to exclude Holidays. Can it be done?

Thanks.