Calculate only working hours between two dates excluding weekends

[Spoorthi]

How should I calculate working hours between two dates? Say if start at 9/25/2009 7:26:13 PM and finish at 10/20/2009 9:46:13 AM, the function should return 245:20:00 because the working hours are from 8am to 11 PM (8 - 23), and there are weekends 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 how can I exclude holidays if there are any.

 

[James006]

Hi Peter,

Your conditions do not appear very clearly ...

If you are to count the number of days B2-A2 ... you will 19 days ...
And if you multiply this result by 10 ... your will get 190 hours ...

Does it help ?

 

[unclebigpete]

It's all solved now thanks James. I was under some time pressure to solve this, and when I hadn't had a reply for a few days, I started a new thread with the question, and Board Regular 'cbatrody' solved it for me.

Here's his answer:
Try the following formula in C1:
=NETWORKDAYS(A1,B1)+NETWORKDAYS(B1,B1)*(MOD(B1,1)-1)-NETWORKDAYS(A1,A1)*MOD(A1,1)
This is considering 'Start Date & Time' is in A1 & 'End Date & Time' is in B1

All I had to do was then get the formatting of the cells to give me the correct 'time' format, and it works like magic. I am quite willing to admit that I would NEVER have worked it out. So thanks to all of you genius types who help out in forums like this, you are all worth more money!

 

[Oliver Batubara]

It's difficult to use NETWORKDAYS because that won't count Saturdays.

If you have start time/date in A2 and end time/date in B2.....and holiday dates listed in H1:H10 then this formula will total all the working hours between those 2, given MF hours of 07:00 until 16:00 and Saturday hours of 08:00 until 12:00

=(SUMPRODUCT(INT((WEEKDAY(A2-{2,3,4,5,6,7})+INT(B2)-INT(A2))/7),{9,9,9,9,9,4})-SUMPRODUCT((H$1:H$10>A2)*(H$1:H$10< B2),LOOKUP(WEEKDAY(H$1:H$10),{1,7},{9,4}))+(MOD(B2,1)-MOD(A2,1))*24+IF(WEEKDAY(A2)=7,8,7)-IF(WEEKDAY(B2)=7,12,16))/24
<!--?XML:NAMESPACE PREFIX = B2),LOOKUP(WEEKDAY(H$1 /--><b2),lookup(weekday(h$1:h$10),{1,7},{9,4}))+(mod(b2,1)-mod(a2,1))*24+if(weekday(a2)=7,8,7)-if(weekday(b2)=7,12,16)) p="" 24<="">
format result cell as [h]:mm

If you want to make it more generic then you could set up a table which lists respective start/end times for Sunday to Saturday and then reference that....so if W2:W8 contains the numbers 1 to 7, X2:X8 shows start times Sun to Sat, Y2:Y8 shows the end times (if it's a non working day then start and end should be blank or zero) and Z2 has the formula

=Y2-X2

copied down to show the daily hours.....then the formula would become

=SUMPRODUCT(INT((WEEKDAY(A2-W$2:W$8)+INT(B2)-INT(A2))/7),Z$2:Z$8)-SUMPRODUCT((H$1:H$10>A2)*(H$1:H$10< B2),LOOKUP(WEEKDAY(H$1:H$10),W$2:Z$8))+MOD(B2,1)-MOD(A2,1)+LOOKUP(WEEKDAY(A2),W$2:X$8)-LOOKUP(WEEKDAY(B2),W$2:Y$8)
</b2),lookup(weekday(h$1:h$10),{1,7},{9,4}))+(mod(b2,1)-mod(a2,1))*24+if(weekday(a2)=7,8,7)-if(weekday(b2)=7,12,16))>

Hi Mr Barry Houdini.

So excited to see this forum. I have searched everywhere, and thank God I found this forum from google.

I have case like this.
I want to calculate my officer working hours with conditions like this.
Monday to Friday --> 08:00 - 16:00
Saturday --> 08:00 - 12:00

I have used your formula above, but I have issue with this formula.
1. If I use that formula in --> start : 26September2015 11:00 ; end : 26September2015 23:00, the result of your formula (in [h]:mm format) is 12:00. I want the result is 1:00 because 26 September is saturday, which have end working hour 12:00 so the count will be 12:00 - 11:00 = 1:00.
2. If I use that formula in --> start : 26September2015 11:00 ; end : 28September2015 09:00, the result of your formula is 3:00. I want the result is 2:00 since 1:00 working day in Saturday and just 1:00 working day also in Monday (09:00 - 08:00). I think the formula counted 08:00 to 09:00 as 2 hours.

Please help me in this case.

Really sorry for the bad english.

 

[DeanPiper]

Yes, it will count hours between the two "timestamps" but including only the defined period on Mondays to Fridays, but excluding weekends and/or any holidays defined.

As previously stated it's only guaranteed to give correct results if the start/end times/dates are within working hours.

If the start or end could be at weekends or evenings, for instance, then you can still calculate the hours but you need a more complicated version of that formula.......

Barry (or anyone) this thread has been sleeping for quite a while, but I'd be very interested to know what the 'more complicated' version of the formula would be - for start dates to be potentially outside of working hours.

Scenario:

Actions are raised to my team 24/7 but we ourselves are only in the office from 08:00 - 19:00 (Mon to Friday). I'd like to know the time between the action being raised (in or out of working hours) and the action being closed (usually within our working hours).

I'd be so greatful of any help here! it will save me from excel suicide!

 

[James006]

Hi,

Have you had a look at this thread :

http://www.mrexcel.com/forum/excel-questions/346901-workday-formulas-time-format-work-hours.html

HTH

 

[justair07]

If start date and time is in A1, start date and time in A2 . . .

=((NETWORKDAYS(A1,A2)-2)*15/24)+23/24-MOD(A1,1)+MOD(A2,1)-8/24

returns 245:20:00 for your given dates/times.
Format the cell containing the formula as [h]:mm:ss

Why the -2 after:

=((NETWORKDAYS(A1,A2)

 

[ashish128]

Awesome. It worked great. Many thanks.

 

[ashish128]

Hello DHT, welcome to MrExcel,

Which version of Excel are you using? If you have Excel 2010 or later try using NETWORKDAYS.INTL function like this

=(NETWORKDAYS.INTL(A2,B2,11,H$1:H$10)-1)*("20:00"-"8:00")+IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$10),MEDIAN(MOD(B2,1),"8:00","20:00"),"20:00")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$10)*MOD(A2,1),"8:00","20:00")

where A2 is start date/time, B2 is end/date time and H1:H10 contains holiday dates - that formula will work with any start or end dates/times

Awesome. It worked great. Many thanks.

 

[MilisaRussell]

Hi!

So good news.. I was able to use this formula however, can you kindly breakdown to why we would do some of operations? Such as, (A1,A2)-1)*15/24)? As well as what the MOD function is?

Thanks!

Milisa

 

[MilisaRussell]

You should be able to use a version of the formula I suggested in post #11 - try this

=(NETWORKDAYS(D2,E2)-1)*("17:00"-"8:30")+IF(NETWORKDAYS(E2,E2),MEDIAN(MOD(E2,1),"17:00","8:30"),"17:00")-MEDIAN(NETWORKDAYS(D2,D2)*MOD(D2,1),"17:00","8:30")

custom format result cell as [h]:mm

Hi!

So good news.. I was able to use this formula however, can you kindly breakdown to why we would do some of operations? Such as, (A1,A2)-1)*15/24)? As well as what the MOD function is?

Thanks!

Milisa