Calculating working hours between two dates

B

Bubuto

New Member
Joined
Jul 24, 2002
Messages
15
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
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.
 
Upvote 0
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.
Click to expand...
 
Upvote 0
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))
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
Anyone,

I've been using this formula for quite a while now and it has worked fine.
Code: 
=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. ;-)
 
Upvote 0
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.
Code: 
=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!!
 
Upvote 0
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 -->
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Hours Between 2 Dates Excluding Non-Business Hours ONLY
Replies
7
Views
532
felixstraube
F
C
  • Question Question
Business Hours Mon-Fri 8AM - 5PM between two dates and times
Replies
5
Views
3K
Ganesh1986
G
S
  • Question Question
Turn around times based on days and hours worked
Replies
2
Views
89
smllchng5
S
P
  • Question Question
How to subtract 2 dates and times from each other excl weekends, holidays and only considering working hours during the day?
Replies
1
Views
124
Micron
M
B
  • Solved
Issue with finding time difference taking account of weekends and workdays.
2
Replies
15
Views
401
Joe4
Joe4

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top