输入开始日期和结束日期，将总天数除以月份(减去节假日)? [英] Enter start and end dates, divide total days by months (minus holidays)?

问题描述

我使用Excel 2013，尝试按月划分时段内的总天数，不包括特定的假日和周末.总数将根据用户输入的开始和结束日期来计算.我可以使用NETWORKDAYS计算该期间的总天数.

Using Excel 2013, I am attempting to divide total numbers of days in a period by month, excluding specific holidays and weekends. The total numbers would be calculated from user-entered start and end dates. I am able to calculate the total number of days for the period using NETWORKDAYS.

=NETWORKDAYS(A3, B3, Holidays!$A$2:$A$50)

我还可以使用NETWORKDAYS计算每个月的总天数.以下是我一月份的示例.

I am also able to calculate the total of number of days for each month using NETWORKDAYS. Below is my example for January.

=NETWORKDAYS(DATE(2014,1,1), DATE(2014,1,31), Holidays!$A$2:$A$50)

我还没有构造参数来说明用户按月输入的日期.为了说明这一点，这是我要完成的屏幕截图，但带有公式:

I have yet to structure arguments that account for the user-entered dates by month. To illustrate, here's is a screenshot of what I would like to accomplish, but with formulas:

我有数百种要输入的案件，希望我可以用开始日期和结束日期来计算月份，而不必手动输入.

I have hundreds of cases to enter, and am hoping I can calculate the months with start and end dates rather than entering them manually.

有什么建议吗?谢谢您的时间.

Any suggestions? Thanks for your time.

推荐答案

以下内容似乎对我有用，尽管有点过时.请注意，为使这项工作有效，您的每个列标题都应该是该月的第一天(例如2013年8月1日).但是您可以设置这些日期的格式，以便它们只显示为月份的缩写.

The below appears to be working for me, though it's kind of gnarly. As a note, to make this work, each of your column headers should be the first of the month (like Aug 1, 2013). But you can format these dates so they just show up as the month abbreviations.

要放入D3，然后上下移动的公式:

Formula to put in D3 and then pull across and down:

=IF((IF(AND($A3<D$1,$B3>(E$1-1)),NETWORKDAYS(D$1,(E$1-1),Holidays!$A$2:$A$50),IF(AND($A3>D$1,$B3>(E$1-1)),NETWORKDAYS($A3,(E$1-1),Holidays!$A$2:$A$50),IF(AND($A3<D$1,$B3<(E$1-1)),NETWORKDAYS(D$1,$B3,Holidays!$A$2:$A$50),IF(AND($A3>D$1,$B3<(E$1-1)),NETWORKDAYS($A3,$B3,Holidays!$A$2:$A$50))))))<0,0,(IF(AND($A3<D$1,$B3>(E$1-1)),NETWORKDAYS(D$1,(E$1-1),Holidays!$A$2:$A$50),IF(AND($A3>D$1,$B3>(E$1-1)),NETWORKDAYS($A3,(E$1-1),Holidays!$A$2:$A$50),IF(AND($A3<D$1,$B3<(E$1-1)),NETWORKDAYS(D$1,$B3,Holidays!$A$2:$A$50),IF(AND($A3>D$1,$B3<(E$1-1)),NETWORKDAYS($A3,$B3,Holidays!$A$2:$A$50)))))))

这篇关于输入开始日期和结束日期，将总天数除以月份(减去节假日)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！