Elapsed Days Hours Minutes Excluding Weekends and

问题:

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".

Thanks

Link to sample workbook

回答1:

You can use NETWORKDAYS and NETWORKDAYS.INTL to achieve this

A bit of manipulation is required as these return net whole days:

• Use these functions to calculate the number of non workdays, then subtract from the difference between start and end dates

  =E3-D3-(NETWORKDAYS.INTL(D3,E3,"0000000")-NETWORKDAYS(D3,E3,$A$16:$A$24))
  

This returns the working day difference, where 1.0 = 1 day

• NETWORKDAYS.INTL(D3,E3,"0000000") calculates whole days between the two dates (no weekends, no holidays)

• NETWORKDAYS(D3,E3,"0000000",$A$16:$A$24) calculates whole working days days between the two dates (Sat/Sun weekends, holidays as per your list in $A$16:$A$24)

• Difference in non-working days between the two dates.

• E3-D3 is time between start and end date/times (1.0 = 1 day)

---

Use custom number formatting to display thye result in the format you require

d "Days" h "Hours" mm "Mins"

Note: this format won't work for negative values, you will need an alternative for when end date is before start date.

回答2:

The following formula works like a treat with no additional formatting or manipulation.

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.

Replacing the "D5" in the formula with your cell reference for your course or metric "End Date" and "E5" with your course or Metric for "Completion Date".

=IF(E5<D5,"-"&TEXT(D5-E5,"h:mm"),NETWORKDAYS(D5,E5,({40910,41005,41008,41036,41064,41065,41148,41268,41269,41275,41362,41365,41400,41421,41512,41633,41634}))-1-(MOD(E5,1)<MOD(D5,1))&" days "&TEXT(E5-D5,"h:mm"))