Time difference between two dates and times, taking into account working days

**Jonsocks** · 03-09-2012, 06:11 AM

Hi all,

I've got two columns with the dates and times a case was closed. I need to work out the amount of time that passed between the case being opened and the case being closed, but I also need this to take into account weekends and holidays. At present the formula being used (I've inherited this spreadsheet) is NETWORKDAYS, which works to some extent, but the issue we are having is if a case was logged at 17:50 on 7 March and then closed at 08:30 on 8 March, this returns a value of 2, when really we'd want that to return zero. I need it to count a working day once 24 hours has passed. Is this possible, been trying to work this out for a while but now my head has stopped working.

Thanks in advance as always

Jon

**Pepe Le Mokko** · 03-09-2012, 06:15 AM

Hi
instead of using the startdate ( say in A1) as is replace it in the networkdays function with =int(A1)+1 and the enddate with =int(A2)-1

**Jonsocks** · 03-09-2012, 07:33 AM

Ah thanks, it's not 100% what I'm after, but I honestly don't think we'll be able to achieve that with formula so it's close enough!

Thanks again

Jon

**daddylonglegs** · 03-09-2012, 08:19 AM

Originally Posted by Jonsocks

....but I honestly don't think we'll be able to achieve that with formula ....

I think you can.....

assuming cases are only opened or closed on working days then you can count full days like this

=NETWORKDAYS(A2,B2,holidays)-1-(MOD(B2,1)<MOD(A2,1))

for your example that will give zero....and should work in all other circumstances