Calculating working hours between two dates

[Bubuto]

How should I calculate working hours between two dates? Say if start at 26 july at 15:00 and finish at 29 july at 10:00, the function should return 4 hours because the working hours are from 8am to 5pm (8 - 17), and there is a weekend between the dates. Preferably the function should work like the NETWORKDAYS() function, but it should also include the time, not just the dates. And also, if have to add say 8 hours to a date, how should I calculate the result? Also this function should aware of the working hours and holidays, so it should ignore those times.

regards,
Petteri Toukoniitty

 

[jagmohan11]

You could try
=IF(NETWORKDAYS(A1,B1)=1,(MIN(Z1,MOD(B1,1))-MAX(Y1,MOD(A1,1))),(NETWORKDAYS(A1,B1)-2)*(600/1440)+(Z1-MAX(MOD($A$1,1),Y1))+MIN(MOD($B$1,1),Z1)-Y1)

where
a1 is start time
b1 is stop time
y1 is start of day (8:00)
z1 is end of day (17:00)

You can certainly change y1 and z1 to defined names if you wish. I beleive this formula should work in all cases, except if the start time falls on a weekend. Please let me know if you find some instances it doesn't work and we can tweak it.

=IF(NETWORKDAYS(A1,B1)=1,(MIN(D1,MOD(B1,1))-MAX(C1,MOD(A1,1))),(NETWORKDAYS(A1,B1)-2)*(D1-C1)+(D1-MAX(MOD($A$1,1),C1))+MIN(MOD($B$1,1),D1)-C1)

Pls try my version where
A1: Start Time eg 01.01.2009 7:00:00 AM
<table x:str="" style="border-collapse: collapse; width: 101pt;" border="0" cellpadding="0" cellspacing="0" width="134"><tr style="height: 12.75pt;" height="17"><td class="xl25" style="height: 12.75pt; width: 101pt;" x:num="39814.291666666664" align="right" height="17" width="134">
</td></tr></table>B1: ENd Time eg 03.01.2009 10:00 AM

C1: Businss hr start time :eg 8:00 AM
C1: Business hr end time :eg 5:00 PM
I think there was an error in the earlier formula. 600 made it to return 10 hrs for each day in next week which I have corrected

 

[maneesh_mathpal]

This works fine, thank you! I'm just wondering, how to add hours to some date. So if some program starts at 2002-07-26 9:00 and lasts for ten hours, how do I calculate is so that it excludes the non-workign hours and holidays? So in this case the answer would be 2002-07-29 10:00, because the working hours are 8 - 17.

Petteri Toukoniitty

If i don't want to exclude the week ends?? how to calculate the working hours in all days??

 

[barry houdini]

Are the working hours the same for all days?

Will start and end times always be in working hours?

If the answer to both of those is yes then this formula will calculate all working hours between a start time/date in A2 and an end time/date in B2, assuming working hours of 08:00 to 17:00

=(INT(B2)-INT(A2))*("17:00"-"8:00")+MOD(B2,1)-MOD(A2,1)

custom format result cell as [h]:mm

 

[maneesh_mathpal]

Not working
<TABLE style="WIDTH: 293pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=390 border=0><COLGROUP><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4864" width=133><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4864" width=133><TBODY><TR style="HEIGHT: 28.5pt; mso-height-source: userset" height=38><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 93pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 28.5pt; BACKGROUND-COLOR: #9999ff" width=124 height=38>CST Creation Date</TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 100pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #9999ff" width=133>CST Case Close Date</TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 100pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #9999ff" width=133>Hours</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: white" height=19>09-Feb-2010 21:42:37</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white">10-Feb-2010 11:35:55</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white">-0.11</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: white" height=19>09-Feb-2010 23:27:05</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white">10-Feb-2010 13:20:49</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white">-0.10</TD></TR></TBODY></TABLE>

 

[barry houdini]

Will start and end times always be in working hours?

I assume that 23:27 is outside working hours (although you don't say what those are) so that formula isn't appropriate. For accommodating any time of day as start and end times but only calculating hours between 08:00 and 17:00 change to

=(INT(B2)-INT(A2))*("17:00"-"8:00")+MEDIAN(MOD(B2,1),"8:00","17:00")-MEDIAN(MOD(A2,1),"8:00","17:00")

although it might be easier to put the daily start and end times in cells and reference those, e.g. start time (e.g. 08:00) in Z2 and end time (e.g. 17:00) in Z3 then formula becomes

=(INT(B2)-INT(A2))*(Z3-Z2)+MEDIAN(MOD(B2,1),Z2,Z3)-MEDIAN(MOD(A2,1),Z2,Z3)

 

[ChiknNutz]

Maybe I've missed something along the way, but much of this seems to greatly overcomplicate the formula. Not sure I get why it matters the "working hours" (of between 8:00 and 5:00 ) in the above example as compared to actual hours worked as the time on the clock is what seems to matter. I have a similar need, that's how I found this thread. However, I'm using a greatly simplified formula that works perfectly for my needs, which may not satisfy this one. Anyway, in my case, the actual start and stop times are unimportant as all I care about is the actual time worked (or more specifically, the time available). Essentially what I'm after is the amount of working hours available between two dates and I've also factored in if Overtime will be used or not. So my formula is simply:

=(NETWORKDAYS([Start Date],[End Date]))*([Hours per day] * (1 + [Overtime percentage]))

 

[Kyriacos Yerou]

Do you have any answer? I have the same problem

Thanks

 

[Kyriacos Yerou]

IML,

Disregard my last, I don't know why some of my times were showing up as whole hours instead of with minutes, unless it's related to my current problem...

If the start time is beyond working hours (Y1, Z1) then I get ############. A negative date?

Do you know how I could allow for start and ending times outside the working hours?

Thanks.

I have the same problem with negative values. Do you have any solution?

Thanks

 

[barry houdini]

Hello Kyriacos,

This is a very old thread - the original question was posted nearly 7 years ago so you may not get any replies from the original protagonists. I note you posted your question in a new thread - which I answered - I suggest you continue there

 

[matt_symes]

For what it's worth - here is my solution to this problem.

Calculating the Working Hours between two date times.

This formula discounts any hours worked outside of a standard working week. i.e. early morning doesn't count. Evening doesn't count. Weekends don't count!


In the formula below I envisage that work_start_time, work_end_time and work_hours_in_day are named excel cells:


work_start_time is the starting time of a working day e.g. 09:00
work_end_time is the end time of a working day e.g. 17:00
work_hours_in_day is just 24*(work_start_time - work_end_time)



=(NETWORKDAYS(start_date_time,end_date_time)*work_hours_in_day)-IF(WEEKDAY(start_date_time,2)>=6,0,MIN(MAX(24*(MOD(start_date_time,1)-work_start_time),0),hours_in_day)) - IF(WEEKDAY(end_date_time,2)>=6,0,MIN(24*MAX((work_end_time-MOD(end_date_time,1)),0),hours_in_day))


Where start_date_time and end_date_time mark the period of interest for which we want to determine the elapsed working hours.


This formula (in as much as I've tested) correctly deals with all potentially messy cases (e.g. starting / ending outside of normal hours on a weekday. Starting / Ending at any time during the weekend. Starting / Ending on Same Day. Starting after working hours and ending later the same day. etc.)

From examining the formula you might be able to determine the general approach I took - which was:

1)Determine the number of working days between the dates.
2)Scale this up into the total number of possible hours that could have been worked over these dates
3)Subtract the inelligible hours from the start day
4)Subtract the inelligible hours from the end day

Hope this is of use to someone.