count timedifference between 2 times without weekends

**christiaan.spriet** · 06-11-2013, 08:41 AM

i want a formula to count the amount of days and hours between 2 times.
i'm working in office 2010.
and i'm using the 24hour timerate.

i have for example 2 dates where i would like to know the amount of time it took between the 2 but without the weekends
so for example:
a1:7/2/2013 08:46
b1: 11/02/13 12:00
c1: the time inbetween without the weekends.

i've already seen some formulas like the networkdays and then you have to use the int function.
but haven't seen any example as my ow (or i didn't manage to convert to my own)

thx for the help in advance

**Kevin UK** · 06-11-2013, 12:31 PM

Deleted post.

**newdoverman** · 06-11-2013, 04:46 PM

Format the answer cell as dd h:mm

enter this formula in that cell: =(B1-A1)-2

oops...that isn't rght

I had to add helper columns to get what appears to be the solution. Perhaps the process can be modified to shorten it. I tried but ran out of time.

**Kevin UK** · 06-12-2013, 01:11 AM

Hi christiaan.spriet

As you have Excel 2010, you could use the NETWORKDAYS.INTL. Try the following formula in C1, format cells as "[h]:MM" (without the quotes) to display the time in hours.

Formula:

Please Login or Register  to view this content.

**daddylonglegs** · 06-12-2013, 08:46 AM

Originally Posted by christiaan.spriet

i have for example 2 dates where i would like to know the amount of time it took between the 2 but without the weekends
so for example:
a1:7/2/2013 08:46
b1: 11/02/13 12:00
c1: the time inbetween without the weekends.

So what's the expected result for C1? I assume it's 2:03:14 (formatted as d:hh:mm)

d:hh:mm format is problematic because d doesn't go any higher than 31, so if you have time periods that may be larger than 31 working days you won't be able to use that format. For periods shorter than 31 working days you can use this formula

=NETWORKDAYS(A2,B2)-1+MOD(B2,1)-MOD(A2,1)

formatted as d:hh:mm

or for any period try this formula

=NETWORKDAYS(A2,B2)-1-(MOD(B2,1)<MOD(A2,1))&" days "&TEXT(B2-A2,"hh:mm")

the latter produces a text formatted result so that result is harder to use in any other calculation

Note: I'm assuming that start and end times won't be at weekends, if they are then the above formulas may return incorrect results

See attached examples where columns A and B contain random (weekday) date/times - press F9 to re-generate