仅用于将来期间的两个日期之间按月计算天数的公式 [英] Formula to calculate days by month between two dates for future periods only

问题描述

努力想出一个适用于此的公式(A1-E4中的数据)

Struggling to come up with a formula that works for this (Data in A1 - E4)

StartDate   EndDate Jan Feb Mar
12/4/2018   4/20/2019   31  28  31
9/26/2018   1/30/2019   30  0   0
1/1/2019    3/31/2019   31  28  31
1/1/2015    3/31/2019   155 141 155

在单元格C2中，公式为:

IN Cell C2, formula is:

=SUMPRODUCT(--(TEXT(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),B2))),"mmm")=C$1))

我想做的是从总和中排除过去的日期，例如在第四行中，一月/二月/三月应为31/28/31，不计入前几年.

What I am trying to do is exclude past dates from the sum, for example in the 4th row, the Jan/Feb/Mar should be 31/28/31 and not count previous years.

有什么想法我要去哪里吗?

Any ideas where I am going wrong?

推荐答案

为年份添加chck:

(YEAR(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),$B2))))=YEAR(TODAY()))

，然后将它们乘以SUMPRODUCT:

and just multiply them in the SUMPRODUCT:

=SUMPRODUCT((YEAR(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),$B2))))>=YEAR(TODAY()))*(TEXT(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),$B2))),"mmm")=C$1))

一个注意事项:

INDIRECT易变性，每次Excel重新计算时都会重新计算.我更喜欢用INDEX代替它们:

INDIRECT is Volatile and will re-calc each time Excel re-calcs. I prefer to replace them with INDEX:

=SUMPRODUCT((YEAR(ROW(INDEX($A:$A,$A2) :INDEX($A:$A, IF($B2="",TODAY(),$B2))))>=YEAR(TODAY()))*(TEXT(ROW(INDEX($A:$A,$A2) :INDEX($A:$A, IF($B2="",TODAY(),$B2))),"mmm")=C$1))

这篇关于仅用于将来期间的两个日期之间按月计算天数的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！