compare 2 dates/times that excludes sundays

September 6, 2011 at 11:40:34
Specs: Windows XP
I have 2 columns Created Time and Resolved Time. Each column has the date and time included (these are extracted from an SQL server in this format). I need to compare the the 2 columns to receive the number of Days to Complete, and this needs to exclude Sundays and Holidays. the formula I'm currently using is: NETWORKDAYS.INTL(D2,E2,11,Holidays!A:A). My problem is that I need it to look at the time also and give me days by the hour. For example:

Created Time: August 31, 2011 20:55
Resolved Time: August 31, 2011 21:55

Current formula returned Days to Complete = 1
I need it to return as 0.04


See More: compare 2 dates/times that excludes sundays

Report •


#1
September 6, 2011 at 12:21:11
Do not have 2010 so can't try out the function NETWORKDAYS.INTL

But the simplest way I can think of is to wrap it in an =IF() statment:

=IF(NETWORKDAYS.INTL(D2,E2,11,Holidays!A:A)=1,E2-D2,NETWORKDAYS.INTL(D2,E2,11,Holidays!A:A))

MIKE

http://www.skeptic.com/


Report •

#2
September 6, 2011 at 12:38:22
Thank you Mike. This is definetely one step closer than I was! However, this only works when it is the same day (see below):

Created Time Resolved Time Days to Complete
Aug 28, 2011 07:49 AM 2011-09-02 14:11:40.0 5
Aug 28, 2011 08:23 AM 2011-08-29 16:40:43.0 1.345636574
Aug 28, 2011 08:28 AM 2011-09-01 15:24:46.0 4
Aug 28, 2011 04:03 PM 2011-08-30 09:35:57.0 2
Aug 28, 2011 05:53 PM 2011-08-29 08:33:46.0 0.611643519
Aug 29, 2011 07:36 AM 2011-08-31 15:17:44.0 3
Aug 29, 2011 08:26 AM 2011-08-29 14:07:31.0 0.237164352
Aug 29, 2011 09:10 AM 2011-08-29 10:33:54.0 0.058263889
Aug 29, 2011 07:23 PM 2011-08-31 15:19:22.0 3
Aug 29, 2011 08:23 PM 2011-08-30 12:06:15.0 2
Aug 29, 2011 11:31 PM 2011-08-30 12:03:48.0 2


Report •

#3
September 6, 2011 at 13:27:00
Try this:

=IF(NETWORKDAYS(D2,E2,Holidays)>1,NETWORKDAYS(D2,E2,Holidays),IF(E2-D2>1,1,E2-D2))

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
September 6, 2011 at 13:37:18
Thanks for responding so quickly. Unfortantely, same result as the last one.

Report •

#5
September 6, 2011 at 15:26:59
Like I said I do not have 2010, so not sure why it's not working with the =NETWORKDAYS.INTL,
but using the =NETWORKDAYS() function with your data I get:

          D                 E             F
08/28/2011  7:49      09/2/2011 14:11     5
08/28/2011  8:23     08/29/2011 16:40     1
08/28/2011  8:28      09/1/2011 15:24     4
08/28/2011 16:03     08/30/2011  9:35     2
08/28/2011 17:53     08/29/2011  8:33     0.611643519
08/29/2011  7:36     08/31/2011 15:17     3
08/29/2011  8:26     08/29/2011 14:07     0.237164352
08/29/2011  9:10     08/29/2011 10:33     0.058263889
08/29/2011 19:23     08/31/2011 15:19     3
08/29/2011 20:23     08/30/2011 12:06     2
08/29/2011 23:31     08/30/2011 12:03     2

Seems to work for me.

MIKE

http://www.skeptic.com/


Report •

#6
September 6, 2011 at 15:35:19
I'm getting that also, but I need it to calculate day/time for each result.

For example:
08/28/2011 7:49 09/2/2011 14:11 5
I need to return with 5.28 (?) because according to the time it was resolved vs. the time it was opened, it was technically more than 5 days.


Report •

#7
September 6, 2011 at 16:04:43
Not sure you can get the hours using =NETWORKDAYS.INTL as it only returns the number of Days.

The only way to get the hours is to subtract the dates/times and since you want to exclude Holidays and Sundays, not sure how it could be done.

Perhaps someone else has some ideas.

Sorry.

MIKE

http://www.skeptic.com/


Report •

#8
September 6, 2011 at 16:08:37
Thanks Mike. I appreciate your assistance.

Report •

#9
September 6, 2011 at 16:21:56
Try here, looks like just what you need:

http://www.cpearson.com/excel/DateT...

MIKE

http://www.skeptic.com/


Report •


Ask Question