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

"Jani Ruohomaa" wrote:

> 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,

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

Mike

"Jani Ruohomaa" wrote:

> 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
>

Sat, 26 Jan 2008 11:45:20 +0200 from Jani Ruohomaa
<(E-Mail Removed)>:
> 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.

--
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/

Mike H kirjoitti:
> Jani,
>
> I should add, format the result as a number with zero decimal places.
>
> Mike
>
> "Jani Ruohomaa" wrote:
>
>> 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
>>
Thanks a million, it seems to work!

Hello Jani,

Do Saturdays and Sundays count, too?

If you want to count indicidual hours per weekday:
http://www.sulprobil.com/html/count_hours.html

Regards,
Bernd

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,
>
> I should add, format the result as a number with zero decimal places.
>
> Mike
>
> "Jani Ruohomaa" wrote:
>
>> 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
>>

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

"Jani Ruohomaa" wrote:

> 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,
> >
> > I should add, format the result as a number with zero decimal places.
> >
> > Mike
> >
> > "Jani Ruohomaa" wrote:
> >
> >> 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
> >>
>