calculating working hours between 2 dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jani Ruohomaa - 26 Jan 2008 09:45 GMT

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

Reply to this Message

Mike H - 26 Jan 2008 10:06 GMT

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

> Hi!
>
[quoted text clipped - 27 lines]
> Thanks,
> Jani

Reply to this Message

Mike H - 26 Jan 2008 10:10 GMT

Jani,

I should add, format the result as a number with zero decimal places.

Mike

> Hi!
>
[quoted text clipped - 27 lines]
> Thanks,
> Jani

Reply to this Message

Jani Ruohomaa - 26 Jan 2008 12:37 GMT

Mike H kirjoitti:

> Jani,
>
[quoted text clipped - 33 lines]
>> Thanks,
>> Jani

Thanks a million, it seems to work!

Reply to this Message

Jani Ruohomaa - 26 Jan 2008 13:49 GMT

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:

> Jani,
>
[quoted text clipped - 33 lines]
>> Thanks,
>> Jani

Reply to this Message

Mike H - 26 Jan 2008 14:36 GMT

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

> Hi!
>
[quoted text clipped - 51 lines]
> >> Thanks,
> >> Jani

Reply to this Message

Stan Brown - 26 Jan 2008 10:16 GMT

Sat, 26 Jan 2008 11:45:20 +0200 from Jani Ruohomaa
<jani.ruohomaa@kolumbus.fi>:

> Does anyone know how to calculate working hours between 2 dates ?

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.

Signature

Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/

Reply to this Message