我正在尝试创建一个公式,以便我可以计算每个客户酒店住宿的晚数 . 很容易 .
但是在我的电子表格中,我一年中有12列(C列以上),A列有到达日期,B列有出发日期 .
如果抵达日期是2014年6月10日B列的出发日期是2014年7月13日,我需要C列以后的公式来计算与该月相关的夜晚/天数,即Junes(H列)将是21天和7月将是12天(因为他们在第13天离开,这不计算在内) . 其他月份列将不返回任何结果 .
为简单起见而不是“1月”“二月”等在C1:N1我只放了数字(1-12)
我在这里假设两年的开始和结束日期是相同的(如果没有更多的编码,但你只提到12个月的列...)
我还假设我们正在研究A2中的开始日期和B2中的完成日期 .
C2的公式:
=IF(AND(MONTH($A2)=C1,MONTH($B2)=C1),$B2-$A2, IF(MONTH($A2)=C1,1+EOMONTH($A2,0)-$A2, IF(MONTH($B2)=C1,$B2-DATE(YEAR($B2),C1,1), IF(AND(MONTH($A2)<C1,MONTH($B2)>C1),DAYSINMONTH(DATE(YEAR($A2),C1,1)) ,0))))
复制/拖动到D2:N2
说明:
如果开始日期和结束日期在同一个月,只需计算差异,请在月份编号匹配的列中使用 .
现在我们知道开始和结束月份不一样了 . 如果开始月份=参考月份,那么天数将由 last_day_of_month - start date 计算(第一晚为1)
last_day_of_month - start date
现在我们知道开始和结束月份不一样,而参考月份不是开始月份 . 如果参考月份是最后一个月,那么天数将是 final_date - first_day_of_month
final_date - first_day_of_month
如果以上都不是参考月份完全在我们的日期范围内或在它之外 . 如果start_month <参考月份<final_month则天数=该月份的所有天数(正确年份)
1 回答
为简单起见而不是“1月”“二月”等在C1:N1我只放了数字(1-12)
我在这里假设两年的开始和结束日期是相同的(如果没有更多的编码,但你只提到12个月的列...)
我还假设我们正在研究A2中的开始日期和B2中的完成日期 .
C2的公式:
=IF(AND(MONTH($A2)=C1,MONTH($B2)=C1),$B2-$A2, IF(MONTH($A2)=C1,1+EOMONTH($A2,0)-$A2, IF(MONTH($B2)=C1,$B2-DATE(YEAR($B2),C1,1), IF(AND(MONTH($A2)<C1,MONTH($B2)>C1),DAYSINMONTH(DATE(YEAR($A2),C1,1)) ,0))))
复制/拖动到D2:N2
说明:
如果开始日期和结束日期在同一个月,只需计算差异,请在月份编号匹配的列中使用 .
现在我们知道开始和结束月份不一样了 . 如果开始月份=参考月份,那么天数将由
last_day_of_month - start date
计算(第一晚为1)现在我们知道开始和结束月份不一样,而参考月份不是开始月份 . 如果参考月份是最后一个月,那么天数将是
final_date - first_day_of_month
如果以上都不是参考月份完全在我们的日期范围内或在它之外 . 如果start_month <参考月份<final_month则天数=该月份的所有天数(正确年份)