PC Review


Reply
Thread Tools Rate Thread

calculating working hours between 2 dates

 
 
Jani Ruohomaa
Guest
Posts: n/a
 
      26th Jan 2008
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 With Quote
 
 
 
 
Mike H
Guest
Posts: n/a
 
      26th Jan 2008
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
>

 
Reply With Quote
 
Mike H
Guest
Posts: n/a
 
      26th Jan 2008
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
>

 
Reply With Quote
 
Stan Brown
Guest
Posts: n/a
 
      26th Jan 2008
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/
 
Reply With Quote
 
Jani Ruohomaa
Guest
Posts: n/a
 
      26th Jan 2008
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!
 
Reply With Quote
 
Bernd P
Guest
Posts: n/a
 
      26th Jan 2008
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
 
Reply With Quote
 
Jani Ruohomaa
Guest
Posts: n/a
 
      26th Jan 2008
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
>>

 
Reply With Quote
 
Mike H
Guest
Posts: n/a
 
      26th Jan 2008
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
> >>

>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating dates based on two dates from 24 hours Alaska1 Microsoft Access 1 27th May 2010 10:04 PM
calculating working hours between 2 dates Jani Ruohomaa Microsoft Excel Discussion 6 26th Jan 2008 02:36 PM
calculating working hours between 2 dates Jani Ruohomaa Microsoft Excel Worksheet Functions 7 26th Jan 2008 02:36 PM
Calculating time increments from dates during working hours S Davis Microsoft Excel Worksheet Functions 0 24th Oct 2006 03:32 PM
Calculating Hours Between 2 Dates & Removing Weekend Dates dbennett@bennettgraphics.com Microsoft Excel Discussion 4 31st Oct 2005 09:01 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 10:57 AM.