How to calculate elapsed time between to days excluding weekends.

Originally Posted by DonkeyOte

Are you unable to test ?

Just tested it and it works of for date range in the same day.

But not for range greater than 30 days for example
08/07/2009 17:05 - 03/11/2009 09:29 is returning 23 days, 16 hrs and 23 mins

I really need one formula that works for both scenarios.

Thanks

Originally Posted by daddylonglegs

The number format dd:hh:mm doesn't work for durations over 31:59:59 because the dd part is actually a day of the month so 31 is actually 31st January, go to 32 days and it reverts to 1 (1st Feb).

You either have to use decimals, i.e. just format as a number with 2 decimal places so you'd get 83.68 for the above example (0.68 of a day representing 16:23)....or if you want to see 83:16:23 then try this formula

=NETWORKDAYS(C10,D10)-1-(MOD(C10,1)>MOD(D10,1))&":"&TEXT(D10-C10,"hh:mm")

Note: this gives a text result so it's not so easy to use in any further calculations you may require

Thanks for your help but i really need to represent the data as

26/08/2009 10:38 09/11/2009 16:30 53 days, 05 hrs and 52 min

and not as

40051.44 40126.69 53:05:52.

Any other solutions will be greatly appreciated.

I wasn't suggesting that you change the format of C10 and D10, you can leave those untouched. To get the result in the format "53 days, 05 hrs and 52 min" you can just tweak the formula I suggested, i.e.

=NETWORKDAYS(C10,D10)-1-(MOD(C10,1)>MOD(D10,1))&" days, "&TEXT(D10-C10,"hh"" hrs and ""mm""min""")

Originally Posted by daddylonglegs

I wasn't suggesting that you change the format of C10 and D10, you can leave those untouched. To get the result in the format "53 days, 05 hrs and 52 min" you can just tweak the formula I suggested, i.e.

=NETWORKDAYS(C10,D10)-1-(MOD(C10,1)>MOD(D10,1))&" days, "&TEXT(D10-C10,"hh"" hrs and ""mm""min""")

Thanks, that works a treat!