已用天数 小时 分钟 不包括周末和节假日时间 [英] Elapsed Days Hours Minutes Excluding Weekends and Holidays Time

问题描述

这听起来很简单，但我一直在努力寻找解决方案.在我秃顶之前的任何帮助都会很棒.

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.

我需要一个公式

• 计算两个日期时间值之间的持续时间(天、小时、分钟)(例如 05/12/2012 5:30 PM 并说 07/12/2012 5:下午 45 点);
• 不包括周末和节假日.

我希望公式的结果如下例如2 Days 0 Hrs and 15 Mins".

I would like the result of the formula to read as follows "e.g 2 Days 0 Hrs and 15 Mins".

谢谢

链接到示例工作簿

推荐答案

以下公式的作用就像一种享受，无需额外的格式设置或操作.

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

为了使其更加稳定，我将英国 2012/13 的所有假期日期都转换为Excel 序列号"格式并将它们放在数组括号中.

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.

将公式中的D5"替换为您的课程或指标结束日期"的单元格引用，并将E5"替换为您的课程或完成日期"的指标.

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

这篇关于已用天数 小时 分钟 不包括周末和节假日时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！