Solved Inconsistencies in Formula and exclude weekend dates

December 1, 2016 at 11:08:14
Specs: Windows 7
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:09 1 Days 9 Hour 58 Minutes
10/31/2016 21:13 11/1/2016 7:12 1 Days 9 Hour 59 Minutes
11/1/2016 7:32 11/1/2016 8:38 0 Days 1 Hour 6 Minutes
10/31/2016 10:42 11/1/2016 9:11 1 Days 22 Hour 29 Minutes
10/31/2016 10:05 11/1/2016 9:14 23 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:37 0 Days 6 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:05 23 Hour 57 Minutes


See More: Inconsistencies in Formula and exclude weekend dates

Report •

#1
December 1, 2016 at 12:59:12
✔ Best Answer
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 unformatted data into an Excel spreadsheet.
To learn how to use the < PRE > tags, read this How-To

http://www.computing.net/howtos/sho...

Thanks.

As for the formula, NETWORKDAYS() by default, will calculate the number of working days between two dates.
It will exclude weekends and, if given, holidays.

Also, NETWORKDAYS() includes both 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

http://www.skeptic.com/

message edited by mmcconaghy


Report •
Related Solutions


Ask Question