Taul
Well-known Member
- Joined
- Oct 24, 2004
- Messages
- 767
- Office Version
- 2019
- Platform
- Windows
Hi,
I need some assistance with calculating working hours between two date/time cells. The working hours are 8:30 to 17:30 and the lunch break is 12:30 to 1:45
The date/time value could be outside the working hours but I only want to count the period within the working hours.
I have an existing formula (from Barry Houdini) that calculates the working hours minus the lunch breaks and excludes weekends and it works with date times outside the working hours.
The formula in cell M2 is:-
The date/time stamps are in cells:-
Start Date F2
End Date G2
I have Named Ranges for the date times and holidays used for the standard working week:-
StartTime
StartLun
EndLun
EndTime
Holiday (Range of 15 cells)
BUT … the working hours are fixed for Monday to Friday.
I would like to incorporate different working hours for Friday (8:30 to 15:00) with the same lunch break as Monday to Thursday. Is this possible?
Would anyone be able to assist in modifying the existing formula or perhaps come up with a different approach that would allow different working hours on a Friday?
I need some assistance with calculating working hours between two date/time cells. The working hours are 8:30 to 17:30 and the lunch break is 12:30 to 1:45
The date/time value could be outside the working hours but I only want to count the period within the working hours.
I have an existing formula (from Barry Houdini) that calculates the working hours minus the lunch breaks and excludes weekends and it works with date times outside the working hours.
The formula in cell M2 is:-
Code:
=(NETWORKDAYS(F2,G2,holiday)-1)*(EndTime-StartTime+StartLun-EndLun)+IF(NETWORKDAYS(G2,G2,holiday),MEDIAN(MOD(G2,1), EndTime,StartTime)-MEDIAN(MOD(G2,1), EndLun,StartLun),EndTime-EndLun)-IF(NETWORKDAYS(F2,F2,holiday),MEDIAN(MOD(F2,1), EndTime,StartTime)-MEDIAN(MOD(F2,1), EndLun,StartLun),StartTime-StartLun)
The date/time stamps are in cells:-
Start Date F2
End Date G2
I have Named Ranges for the date times and holidays used for the standard working week:-
StartTime
StartLun
EndLun
EndTime
Holiday (Range of 15 cells)
BUT … the working hours are fixed for Monday to Friday.
I would like to incorporate different working hours for Friday (8:30 to 15:00) with the same lunch break as Monday to Thursday. Is this possible?
Would anyone be able to assist in modifying the existing formula or perhaps come up with a different approach that would allow different working hours on a Friday?