Infiltrator64
Board Regular
- Joined
- Dec 19, 2004
- Messages
- 63
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)))
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)))
