Formula Calculation That Excludes Specific Hours of the Day

If your start and end date/times are always within the working hours you can use this formula

=(NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+MOD(B2,1)-MOD(A2,1)

or if you want to allow any start/end times/dates try

=(NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"8:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"8:00","17:00")

Assumes start date/time in A2 and end date/time in B2 - custom format result cell as [h]:mm

The above gives you results in time format like 28:15 - if you want decimal format like 28.25 then multiply the whole formula by 24 and format result cell as number

Originally Posted by daddylonglegs

If your start and end date/times are always within the working hours you can use this formula

=(NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+MOD(B2,1)-MOD(A1,1)

or if you want to allow any start/end times/dates try

=(NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"8:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A1,1),"8:00","17:00")

Assumes start date/time in A2 and end date/time in B2 - custom format result cell as [h]:mm

The above gives you results in time format like 28:15 - if you want decimal format like 28.25 then multiply the whole formula by 24 and format result cell as number

Start and end times are not always within the reporting threshold I defined.

Also, what format do the date/times need to be in, in their respectice Cells (Cell 1 & Cell 2 in my example)?

Also I notice an A1 in the last line of the formula. What does that represent?

Thanks!

Sorry, that was a typo, all references should be to A2 or B2, i.e.

=(NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"8:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"8:00","17:00")

A2 and B2 should be valid date/time values in excel (e.g. if you re-format as general the values change to numbers) - other than that the exact date/time format doesn't matter

Originally Posted by daddylonglegs

Sorry, that was a typo, all references should be to A2 or B2, i.e.

=(NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"8:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"8:00","17:00")

A2 and B2 should be valid date/time values in excel (e.g. if you re-format as general the values change to numbers) - other than that the exact date/time format doesn't matter

Ok I figured as much with the A1 issue. Thanks. This is the closest I've been to fixing this sheet and I am terrible with Excel.

So the author inserted that dates using MM/DD/YYYY format followed by a time stamp.

For example.

The start date cell (cell 1) reads 1/23/2013 10:00

The end date cell (cell 2) reads 1/24/3013 8:16

The result cell I asked you to calculate reads 1/0/1900 7:16.

what I am reading that as is that the "1/0/1900" represents months days and years, so if the result time is less than 24 hours it will always read 1/0/1900. However if it's say 26 hours then it would read 1/1/1900 2:00. Is that correct?

I'm looking to clean that up so that it's more easily interpreted by the reader.

The format I am trying to achieve is in hours and minutes as a cumulative total. If I format the result to be hh:mm, once it crosses 24 it wants dd in the formating, when I would prefer it keep counting on past 24. Is that possible?