经过几天的时间不包括周末假期时间 [英] 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 PM ）;


• 不包括周末和假期。

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

我想将公式的结果读为遵循例如 2天0小时和15分钟。

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屋！