Calculate working hours between two dates excluding weekends

[micky666]

working hours from 9:00-17:00. Excluding weekends. Have an Excel 2010.

Start Date 1/1/14 08:00
End Date: 3/1/14 11:00

Details above given in:
Date format: MM/DD/YY
24-hour time

Date and Time are in separate Tabs
i.e A1: 1/1/14
B1: 08:00
C1: 3/1/14
D1: 11:00

 

[Andrew Poulsom]

Welcome to MrExcel.

See if this helps you:

Business Hours

 

[micky666]

This does not work i'm not sure if it's because i have the time and date in two different tabs and i'm not sure what the square brackets means: [h]:mm

 

[Andrew Poulsom]

That number format displays hours even if they are greater than 24. What formula are you using and in what way isn't it working?

 

[micky666]

[TABLE="width: 844"]
<tbody>[TR]
[TD]Machine[/TD]
[TD]Failure/Event[/TD]
[TD]System Failed[/TD]
[TD][/TD]
[TD]System Restored[/TD]
[TD][/TD]
[TD]Part Responsible[/TD]
[TD][/TD]
[TD]Start Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Component[/TD]
[TD]Sub-Component[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]F[/TD]
[TD]3/01/2004[/TD]
[TD]11:00:00[/TD]
[TD]3/05/2004[/TD]
[TD]9:00 AM[/TD]
[TD]Electric Saw[/TD]
[TD]Motor[/TD]
[TD="align: right"]1/01/2004[/TD]
[TD="align: right"]8:00:00[/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]F[/TD]
[TD]3/08/2004[/TD]
[TD]9:00:00[/TD]
[TD]3/09/2004[/TD]
[TD]11:00 AM[/TD]
[TD]Electric Saw[/TD]
[TD]Motor[/TD]
[TD="align: right"]1/01/2004[/TD]
[TD="align: right"]8:00:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]F[/TD]
[TD]7/23/04[/TD]
[TD]16:15:00[/TD]
[TD]7/25/04[/TD]
[TD]3:15 AM[/TD]
[TD]Electric Saw[/TD]
[TD]Motor[/TD]
[TD="align: right"]1/01/2004[/TD]
[TD="align: right"]8:00:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
#VALUE! = =(NETWORKDAYS(I4:J4,C4:D4)-1)*("17:00"-"8:00")+MOD(C4:D4,1)-MOD(I4:J4,1)

System failed = system End

 

[barry houdini]

Try like this:

=(NETWORKDAYS(I4,C4)-1)*("17:00"-"8:00")+D4-J4

custom format result cell as [h]:mm as previously advised

 

[micky666]

not that does not work it returns 11 hours which is wroing

 

[Andrew Poulsom]

Did you format the cell as [h]:mm:ss? The square brackets are necessary to show hours greater than 24.

 

[micky666]

when i did it returned #value.

I found an easier way to do it by just =(NETWORKDAYS(I4,C4)-1)*9+11 so you just get the date and X by 9 for the amount of hours in a workday + 11 for the next day.

Thanks to both of you for your help in any case. I greatly appreciate it.

 

[murugan404]

My requirement was to calculate working hours including all days in between two dates, this can be tweaked using =networkdays formula to calculate excluding weekends
A1= 01-12-2014 22:45
B1= 03-12-2014 06:45
C1= 08:00:00
D1= 20:00:00

All above is in the format "DD-MM-YYYY HH:MM:SS"

Formula
=IF((IF(MOD(B1,1)<C1,(D1+(INT(B1)-1)),(MIN(D1,MOD(B1,1))+INT(B1))))-(IF(MOD(A1,1)>D1,(C1+(INT(A1)+1)),(MAX(C1,MOD(A1,1))+INT(A1))))<0,0,((D1-C1)*(IF(MOD(IF(MOD(A1,1)>D1,(C1+(INT(A1)+1)),(MAX(C1,MOD(A1,1))+INT(A1))),1)>D1,(INT(IF(MOD(B1,1)<C1,(D1+(INT(B1)-1)),(MIN(D1,MOD(B1,1))+INT(B1))))-INT(IF(MOD(A1,1)>D1,(C1+(INT(A1)+1)),(MAX(C1,MOD(A1,1))+INT(A1))))-1),(INT(IF(MOD(B1,1)<C1,(D1+(INT(B1)-1)),(MIN(D1,MOD(B1,1))+INT(B1))))-INT(IF(MOD(A1,1)>D1,(C1+(INT(A1)+1)),(MAX(C1,MOD(A1,1))+INT(A1)))))))+(MIN(D1,MOD(IF(MOD(B1,1)<C1,(D1+(INT(B1)-1)),(MIN(D1,MOD(B1,1))+INT(B1))),1))-MAX(C1,MOD(IF(MOD(A1,1)>D1,(C1+(INT(A1)+1)),(MAX(C1,MOD(A1,1))+INT(A1))),1)))))


The cell(where formula is pasted) needs to be formatted in
Time -> Locale (Location): English(U.S) --> 37:30:55