Thanks for the great formulas. However I am getting some inconsistencies with the 1 day, 0 day. Is there a way to only list (1 day) if the amount of time exeeds 24 hours?Plus is there a way to not inlcude weekends in the calculations? =IF(OR(E2="",F2=""),"",IF(NETWORKDAYS(E2,F2,$AA$1:$AA$5)-1=0,HOUR(MOD(F2-E2,1))&" Hour "&MINUTE(MOD(F2-E2,1))&" Minutes",NETWORKDAYS(E2,F2,$AA$1:$AA$5)-1&" Days "&HOUR(MOD(F2-E2,1))&" Hour "&MINUTE(MOD(F2-E2,1))&" Minutes"))

Request date Solved date Duration

10/31/2016 21:11 11/1/2016 7:091 Days 9 Hour 58 Minutes

10/31/2016 21:13 11/1/2016 7:121 Days 9 Hour 59 Minutes

11/1/2016 7:32 11/1/2016 8:380 Days1 Hour 6 Minutes

10/31/2016 10:42 11/1/2016 9:111 Days 22 Hour 29 Minutes

10/31/2016 10:05 11/1/2016 9:1423 Hour 9 Minutes

10/17/2016 16:07 11/1/2016 10:31 11 Days 18 Hour 24 Minutes

10/27/2016 10:58 11/1/2016 11:07 3 Days 0 Hour 9 Minutes

10/7/2016 14:09 11/1/2016 11:08 17 Days 20 Hour 59 Minutes

11/1/2016 7:15 11/1/2016 13:370 Days6 Hour 22 Minutes

10/31/2016 12:05 11/1/2016 13:58 1 Days 1 Hour 53 Minutes

10/31/2016 14:08 11/1/2016 14:0523 Hour 57 Minutes

When you post data, it is much easier it you use the < PRE > tags to align your data. That saves me from try to get your unformatteddata into an Excel spreadsheet.

To learn how to use the < PRE > tags, read this How-Tohttp://www.computing.net/howtos/sho...

Thanks.

As for the formula, NETWORKDAYS() by default, will calculate the number of working days between two dates.

It willexcludeweekends and, if given, holidays.Also, NETWORKDAYS()

includesboth the Start Date and the End Date in it's calculations.So

with a Start Date of: 10/31/2016 21:11

and End Date of: 11/01/2016 07:09

NETWORKDAYS() will return TWO days, The 31st and the 1st.If the Start Date and End Date are the same, it will return 1

So we can modify the formula to this:

=IF(OR(E2="",F2=""),"",IF(NETWORKDAYS(E2,F2,$AA$1:$AA$5)-1<2,HOUR(MOD(F2-E2,1))&" Hour "&MINUTE(MOD(F2-E2,1))&" Minutes",NETWORKDAYS(E2,F2,$AA$1:$AA$5)-1&" Days "&HOUR(MOD(F2-E2,1))&" Hour "&MINUTE(MOD(F2-E2,1))&" Minutes"))

See how that works for you.

MIKE

message edited by mmcconaghy

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History