更新时间:2023-01-29 09:40:22
以下内容似乎对我有用,尽管有点过时.请注意,为使这项工作有效,您的每个列标题都应该是该月的第一天(例如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)))))))