Date/Time Calculation excluding weekends and holiday list not working

**mikeyk80** · 07-30-2012, 02:38 PM

I have a shared workbook that tracks different task metrics. On each sheet, some data elements that are captured is a copy/paste from the date/timestamp from lotus notes email as a begin time and then a macro is set up to populate the end time with the current time. The begin time (7/25/2012 10:11:00 AM) and end time (7/26/2012 8:58:38 AM) are in date with 12 hour format. I have a holiday schedule set up on the "Dropdowns" tab that lists the holidays for the current year in a table. I cannot seem to get Excel to caclulate the proper elapsed time between the begin time and the end time excluding weekend (Saturdays and Sundays) and holiday (my created list) times. I'm trying to output the elapsed time in general format that would give me a TAT that I can round to either tenths or hundreths of a day (i.e. 1.50 days = 36 hours)

**daddylonglegs** · 07-30-2012, 02:48 PM

Will the start and end times always be on working days? If so then with start time/date in A2 and end time/date in B2 try this formula for the number of elapsed days (excluding weekends and holidays) as a decimal

=NETWORKDAYS(A2,B2)-1+MOD(B2,1)-MOD(A2,1)

**mikeyk80** · 07-30-2012, 02:58 PM

Thanks for the heads up. The begin and end times won't necessarily be on a "working" day. What risk does the above run in that case?

**daddylonglegs** · 07-30-2012, 04:03 PM

In that case you can use this formula, assuming you have holiday dates listed in H2:H10

=NETWORKDAYS(A2,B2,H$2:H$10)+NETWORKDAYS(B2,B2,H$2:H$10)*(MOD(B2,1)-1)-NETWORKDAYS(A2,A2,H$2:H$10)*MOD(A2,1)

That will count all hours (midnight to midnight) on working days