计算两个日期之间每个月的天数

计算两个日期之间每个月的天数

本文介绍了Excel-计算两个日期之间每个月的天数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在尝试编写一个excel公式,可以将其拖动到一行单元格上,以给出两个指定日期之间每个月的天数.例如:

I am trying to write an excel formula which can be dragged across a row of cells to give the number of days in each month between two specified dates. E.g:

         A           B         C       D       E       F

1  | START DATE | END DATE |  Jan  |  Feb  |  Mar  |  Apr  |...
   ---------------------------------------------------------
2  |  10/02/17  | 15/03/17 |   0   |  19   |  15   |   0   |...

更为复杂的是,结束日期可能留为空白,指示结束日期应视为今天.我可以在从C2到A&列的单元格中添加一个公式. B必须留给用户输入.

An added complication is that the end date may be left blank indicating that the end date should be treated as today. I can put a formula in the cells starting from C2 but columns A & B must be left for user entry.

我使用许多嵌套的if语句取得了一些进展,但是如果结束日期为空,则很难处理它.

I made some progress using lots of nested if statements but am having real trouble getting it to handle if the end date is blank.

任何人都可以帮忙吗?

*编辑

我避免显示我编写的公式,因为我怕它有点混乱,但根据要求,我现在在下面显示(来自C2的公式)

I refrained from showing the formula I had written so far as I'm afraid it's a bit messy but as it has been requested, I have now shown below (this formula from C2)

 =IF(AND($A2<C$1,$B2<D$1),DAY(A2),IF($A2>=D$1,0,IF(AND($A2<C$1,$B2=""),IF(TODAY()<C$1,0,IF(TODAY()>C$1,IF(TODAY()<D$1,DAY(TODAY()),D$1-C$1),IF(TODAY()<D$1,INT(TODAY()-$A2),$D1-$C1))),IF(AND($A2>=C$1,$B2=""),IF(TODAY()<D$1,INT(TODAY()-$A2+1),D$1-$A2),IF(AND($A2>=C$1,$B2<D$1),$B2-$A2+1,IF(AND($A2<C$1,$B2<C$1),0,IF(AND($A2>=C$1,$B2>=D$1),D$1-$A2,IF(AND($A2<C$1,$B2<D$1),DAY(B2),IF(AND($A2<C$1,$B2>=D$1),D$1-C$1,0)))))))))

推荐答案

无论如何,这是基于标准公式的完整性的替代方法

Anyway here is the alternative approach for completeness based on the standard formula

=max(0,min(end1,end2)-max(start1,start2)+1)

给出两个日期范围之间的重叠

for the overlap between 2 date ranges which gives

=MAX(0,MIN(IF($B2="",TODAY(),$B2),EOMONTH(DATEVALUE(C$1&"-17"),0))-MAX($A2,DATEVALUE(C$1&"-17"))+1)

这篇关于Excel-计算两个日期之间每个月的天数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-14 23:30