Need to subtract two dates/times taking into account holidays, weekends.

**chauhan101** · 05-14-2012, 04:28 PM

My Excel 2010 spreadsheet is a live spreadsheet exported from SharePoint. It tracks user requests which can come in at any time/day.

I have two columns - Created and Completed that I need to understand the difference between the date/time the request is created to when its completed.

Example:
Column Header - "Created" - 5/7/2012 10:07
Column Header - "Completed" - 5/7/2012 12:25

Its in military time.

I need to find the difference between the two times, taking into account holidays and weekends. The requests can come into the system at any point of time however (night /day/holidays/etc)

Your guidance is very much appreciated!

**Alexander_Golinsky** · 05-14-2012, 04:32 PM

have you tried =workday function?

**JBeaucaire** · 05-14-2012, 04:33 PM

The function you want is called NETWORKDAYS() and it allows you to specify holidays to exclude, too.

**chauhan101** · 05-14-2012, 07:07 PM

Thank you for the quick replies. I tried the networkdays but it only brings back days, I'd like to know hours and minutes.
For example if a request was entered at 10am on Monday and completed at 1pm on Monday, the formula will bring back 1 even though its really 3 hours.
This is my formula: =NETWORKDAYS([@Created], [@[Completed Date]], 'Do NOT DELETE Time Frames'!N$2:N$12)

**zbor** · 05-15-2012, 03:13 AM

Maybe this:

=(NETWORKDAYS([@Created],[@[Completed Date]],'Do NOT DELETE Time Frames'!N$2:N$12)-1+([@[Completed Date]]-INT([@[Completed Date]]))-([@Created]-INT([@Created]))

{or for easier to read: =(NETWORKDAYS(A2,B2,'Dates to Exclude'!$A$2:$A$1000)-1)+(B2-INT(B2))-(A2-INT(A2)) }

And format cell as [h]:mm

**chauhan101** · 05-18-2012, 10:24 AM

THANK YOU WORKED GREAT!!!

Now I wanted to add one more thing and I have a question.

I have these two formulas:

This gives me the days/hours/minutes but does not take into account weekends and holidays
=INT([@[Completed Date]]-[@Created])&"days "&INT(MOD([@[Completed Date]]-[@Created],1)*24)&"hrs "&INT(MOD(MOD([@[Completed Date]]-[@Created],1)*24,1)*60)&"mins"

This gives me the days but because it rounds, its too general, it would be nice to see the days/hours/minutes.
=NETWORKDAYS([@Created], [@[Completed Date]], 'Do NOT DELETE Time Frames'!N49:N59)

How can I combine the two formulas to figure out the days/hours/min excluding weekends and holidays?