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

 

[IML]

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.

 

[Bubuto]

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

On 2002-07-25 07:27, IML wrote:
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.

 

[Dave Hawley]

Hi Bubuto

Read this page, and the MS links at the bottom. After that you will be a full bottle on Dates and Times in Excel.

http://www.ozgrid.com/Excel/ExcelDateandTimes.htm

 

[IML]

here is my attempt, not fully tested. This assumes time date in A1, hours likst in B1 (ie enter 10 for ten hours, 10.5 four 10:30, etc). Open time is still Y1 and close time Z1.

=IF(MAX(0,B1/24-MIN($Z$1-$Y$1,MAX($Z$1-MOD(A1,1),0))),WORKDAY(A1,INT(MAX(0,B1/24-MIN($Z$1-$Y$1,MAX($Z$1-MOD(A1,1),0)))/($Z$1-$Y$1))+1)+$Y$1+MOD(MAX(0,B1/24-MIN($Z$1-$Y$1,MAX($Z$1-MOD(A1,1),0)))/($Z$1-$Y$1),1)*($Z$1-$Y$1),A1+(B1/24))

 

[megnin]

IML,

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

Your formula works great for full hours. How would I modify it to show minutes as well?

Thank you!

 

[megnin]

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.

 

[megnin]

Anyone,

I've been using this formula for quite a while now and it has worked fine.

=IF(((J2-A2)<0),"OPEN",MIN(MOD(J2,1),$Z$1)-MAX(MOD(A2,1),$Y$1)+(SUM(IF(WEEKDAY(INT(A2)-1+ROW(INDIRECT("1:"&(INT(J2)-INT(A2))+1)))=TRANSPOSE($Y$3:$Y$7),1,0))-1)*($Z$1-$Y$1))

Now they want to INCLUDE weekends. No other change, just count weekends. Same working hours, etc.

How could I change the formula to count weeks as well. All 7 days.

Thanks!!

I tried to figure out what I needed to delete to make it count all 7 days, but it just made my head hurt.

 

[megnin]

Hi,

StartTime= A2
EndTime= J2
StartWorking Hours= $Y$1
EndWorking Hours= $Z$1
Y2 = "1" (Sunday)
Y3 = "2" (Monday)
Y4 = "3"
Y5 = "4"
Y6 = "5"
Y7 = "6"
Y8 = "7" (Saturday)

How do you calculate working hours (and minutes) between start time and end time. The times may span two or more days.

I've been using this formula for quite a while now and it has worked fine, but excluded weekends.

=IF(((J2-A2)<0),"OPEN",MIN(MOD(J2,1),$Z$1)-MAX(MOD(A2,1),$Y$1)+(SUM(IF(WEEKDAY(INT(A2)-1+ROW(INDIRECT("1:"&(INT(J2)-INT(A2))+1)))=TRANSPOSE($Y$3:$Y$7),1,0))-1)*($Z$1-$Y$1))

Now they want to INCLUDE weekends. No other change, just count all 7 days instead of just mon-fri.

Thanks!!

 

[avoodi]

I am somehow not able to get it to work
I think something wrong in the formating of my cells

I am trying to use the formula given below
=(NETWORKDAYS(H6,N6)-1)*($F$2-$E$2)+MOD(N6,1)-MOD(H6,1)
here H6, N6 are in format of date time (like 3/5/2009 2:11:27 PM)
and F2, E2 are in time format (9:00:00 AM and 6:00:00 PM)

So when i calculate for start date <TABLE style="WIDTH: 81pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=108 border=0 x:str><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_2106812 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 81pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=108 height=17 x:num="39874.312037037038">3/2/09 7:29</TD></TR></TBODY></TABLE>
and end date <TABLE style="WIDTH: 147pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=196 border=0 x:str><COLGROUP><COL style="WIDTH: 147pt; mso-width-source: userset; mso-width-alt: 7168" width=196><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_2106812 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 147pt; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=196 height=17 x:num="39903.761805555558">3/31/09 18:17</TD></TR></TBODY></TABLE>
Its giving me wrong result as 7:29 ... (this result column i have formated as h:mm:ss )
please help
<!-- / message -->