Elapsed Days Hours Minutes Excluding Weekends and Holidays

This sounds simple but I have been pulling my hair out trying to figure out a solution. Any help before I go bald would be great.

I need a formula which is able to calculate the duration in (days, hrs, mins) between two date\time values (eg 05/12/2012 5:30 PM and say 07/12/2012 5:45 PM); excluding weekends and holidays. I would like the result of the formula to read as follows "e.g 2 Days 0 Hrs and 15 Mins".

Bit of context. Trainers delivery courses and mark the courses as ‘Completed’ in an electronic diary system. The diary records a date & time stamp of when the course was marked delivered. A penalty is payable if a trainer does not mark a course as ‘Completed’ within 24hrs of the end time of the course. This 24hr window excludes weekends and holidays (i.e if the course ends on a Friday at 5:00pm they have until Monday 4:59 pm to mark as delivered). Technically a trainer can mark a course delivered up to 2 hours prior to the end time of the course, so this would result in a negative figure if you did a simple ‘End Time’ minus Completed Time’

Elapsed_Completion_Time.xlsx

Originally Posted by mrice

Have you investigated the NETWORKDAYS.INTL function?

I can't see how that would solve my problem. If you could expand your suggestion that would be fab. Thanks for the effort in any case.

Originally Posted by daddylonglegs

Assuming start and end dates will be working days and that negative values will always be in hours try this version in row 3 copied down

=IF(E3<D3,"-"&TEXT(D3-E3,"h:mm"),NETWORKDAYS(D3,E3,A$16:A$24)-1-(MOD(E3,1)<MOD(D3,1))&" days "&TEXT(E3-D3,"h:mm"))

Works like a treat, many thanks daddylonglegs!!! mrice, i'm sure your formula works great also, I opted for the "thinner" forlmula, thank you for your time also.

To make it more stable I have turned all the holiday dates for UK 2012/13 into ‘Excel Serial Number’ format and placed them in an array bracket.

Please Login or Register  to view this content.