Calculating total hours between two cells exc weekends

**finch82** · 10-26-2011, 03:15 PM

Hello,

What I am trying to achieve is to work out the total number of hours when I take away cell B2 from A2. However I need to ensure that weekends are not counted within a date range including weekends.

As I need to work out how many are dealt with within certain timescales such as 24hrs, 48hrs and 72hrs.

I have attached a sample sheet.

Please can you help.

Regards.

Cheers.

**daddylonglegs** · 10-26-2011, 03:36 PM

Your timestamps are formatted as text so you need to convert those to valid date/times, e.g. I converted A2 with this formula in D2

=REPLACE(A2,3,5,"-"&MID(A2,4,3)&"-")+0

and similar in E2 for end time

Assuming all timestamps start on weekday days (Monday to Friday) then you can then get the total hours with this formula

=(NETWORKDAYS(D2,E2)+MOD(E2,1)-MOD(D2,1))*24-24

format as number - see attached

**finch82** · 10-26-2011, 04:02 PM

Thanks for the quick response this works at treat, however could you please explain the formula in depth, if asked about this i would like to be able to explain after fullyunderstanding myself.

I am very new to excel.

Cheers

**daddylonglegs** · 10-26-2011, 04:38 PM

NETWORKDAYS(D2,E2) just returns the number of working days between 2 dates (it ignores the times) but it includes both start and end date so From today (Wednesday) to tomorrow (Thursday) counts as 2.....but that should only count as 1 day for our purposes so I subtract a day or 24, i.e.

=NETWORKDAYS(D2,E2)*24-24

that would give a result of 24 for my example....which would be the right answer if the time was the same on both days.......but assuming it isn't you can use MOD(time/date,1) to extract the time from a time/date so by adding the time from the end time/date and subtracting the time on the start time/date we get the adjusted total, e.g. if D2 is 5 PM today and E2 is 9 AM tomorrow then

=MOD(E2,1)*24-MOD(D2,1)*24 will give you

=9-17 = -8 so overall using

=NETWORKDAYS(D2,E2)*24-24-MOD(E2,1)*24-MOD(D2,1)*24

That results in 2*24-24+9-17 = 16

the numer of hours difference from 5 PM on Wednesday to 9 AM on Thursday

I simplified it a little by factoring out the 24s, could be even simpler , i.e.

=(NETWORKDAYS(D2,E2)-1-MOD(E2,1)4-MOD(D2,1))*24