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]

Machine

Failure/Event

System Failed

System Restored

Part Responsible

Start Date

Date

Time

Date

Time

Component

Sub-Component

Date

Time

1

F

3/01/2004

11:00:00

3/05/2004

9:00 AM

Electric Saw

Motor

1/01/2004

8:00:00

#VALUE!

2

F

3/08/2004

9:00:00

3/09/2004

11:00 AM

Electric Saw

Motor

1/01/2004

8:00:00

2

F

7/23/04

16:15:00

7/25/04

3:15 AM

Electric Saw

Motor

1/01/2004

8:00:00

<tbody>
</tbody>

#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