Working Time

[OrangeAndy]

Hi,

I would like to be able to calculate the working time between a star
date/time (dd/mm/yyyy hh:mm:ss) and an End date (dd/mm/yyyy hh:mm:ss)

In this case the working time should be considered 08:00 - 18:00 Monda
to friday, therefore I need to exclude anytime outside this window.
Please HELP !!!

Thanks
And

 

[Daniel.M]

Hi Andy,

In your case:
OpStart = 8:00
OpEnd = 18:00
StartDT : Your start date/time
EndDT : Your end date/time

=IF(StartDT>EndDT,0,NETWORKDAYS(StartDT,EndDT)*(OpEnd-OpStart)
-IF(NETWORKDAYS(StartDT,StartDT),MAX(0,MIN(OpEnd,MOD(StartDT,1))-OpStart),0)
-IF(NETWORKDAYS(EndDT,EndDT),MAX(0,OpEnd-MAX(MOD(EndDT,1),OpStart)),0))

You can add an Holidays range (to exclude those) as the 3rd argument to
NETWORKDAYS() if you see fit. More details on the NETWORKDAYS() help.

You can search group/google for "NetWorkHours" and "WorkHours" on this NG if
you'd like a VBA solution (H. Grove and myself, amongst others, have written
implementations) instead of this formula.

Regards,

Daniel M.