J
Jani Ruohomaa
Hi!
Does anyone know how to calculate working hours between 2 dates ?
The 2 examples I found on the Internet does not work
For example this one:
=IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT),HolidayList,0))))
,0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((EndDT-INT(EndDT))-
(StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+
(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))
StartDT: 8.1.2008 05:00
EndDT: 8.1.2008 08:00
DayStart: 8:00
DayEnd: 16:00
gives as a result 3 hours (it should of course be 0)
Does anyone know how to fix it so that it calculates correctly ?
There is another solution for calculating the working hours between 2
dates but it has the same problem
Thanks,
Jani
Does anyone know how to calculate working hours between 2 dates ?
The 2 examples I found on the Internet does not work
For example this one:
=IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT),HolidayList,0))))
,0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((EndDT-INT(EndDT))-
(StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+
(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))
StartDT: 8.1.2008 05:00
EndDT: 8.1.2008 08:00
DayStart: 8:00
DayEnd: 16:00
gives as a result 3 hours (it should of course be 0)
Does anyone know how to fix it so that it calculates correctly ?
There is another solution for calculating the working hours between 2
dates but it has the same problem
Thanks,
Jani