# How can I calculate time durations for only business hours?

#### Infiltrator64

##### Board Regular
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)))

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
Re: How can I calculate time durations for only business hou

Hi

In response to this part :

Infiltrator64 said:
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.

Perfect! it works.

Thanks Andrew this works perfectly.

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.

Replies
1
Views
131
Replies
5
Views
288
Replies
1
Views
583
Replies
4
Views
265
Replies
1
Views
1K

1,203,600
Messages
6,056,205
Members
444,850
Latest member
dancasta7

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back