calculating working hours between 2 dates

  • Thread starter Thread starter Jani Ruohomaa
  • Start date Start date
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
 
Jani,

Maybe this:-

=((NETWORKDAYS(A1,B1)-1)*(D$2-D$1)+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),D$2,D$1),D$2)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),D$2,D$1))*24

Where

A1 = Start date & Time
B1 = End Date/Time
D1 =day start time
D2 =Day end time

The formula can be dragged down for different start/end periods in columns A
& B.

Mike
 
Sat, 26 Jan 2008 11:45:20 +0200 from Jani Ruohomaa
Does anyone know how to calculate working hours between 2 dates ?
Click to expand...

Look in Help for NETWORKDAYS, and multiply by the number of working
hours per workday.

You will need Analysis Toolpak if it's not already installed.
 
Hi!

It seems that it gives as a result for these to dates 16 hours but it
should be 0 since 12th and 13th are saturday and sunday and non-working days

start date 12.1.2008 08:00
end date 13.1.2008 08:00

If I put

start date as 11.1.2008 08:00

and end date as 12.1.2008 08:00 or 13.1.2008 08:00 it is correct, 8 hours
11th is a friday and a working day

Jani


Mike H kirjoitti:
 
Hi,

On my computer it gives a result of 0 for those dates/times which is correct
because as you point out they are a saturday and sunday
If I extend the dates to
12/01/2008 08:00 14/01/2008 08:00
This also returns (correctly) 0 and
12/01/2008 08:00 14/01/2008 16:00
Returns 8

I can't explain the erronoeous results you are getting, the formula is
correct.

Mike
 
Back
Top