I have this situation:
1.ods (39.0 KB)
I want to make a range of dates that works whether it is a leap year or not. That means that the last row must not repeat the first day of the year. My year starts on 1 december.
The sum for december works if is a leap year, but it doesn’t if is not, i get a value error.
How can i fix it?
Thank you.
@PKG
Now the problem is how to show a message like “-” on A357 cell when is not a leap year, and to show the last day of the year when it is a leap year, without messing up the formulas?
Couldn’t find anything better than this, to let the last cell show a date even if is the next year and use this formula:
=SUMPRODUCT(MONTH($A$3:$A$368)=MONTH($A370),YEAR($A$3:$A$368)=YEAR($A370),B$3:B$368)
What’s “it” ? Your date range has parts in two years.
30.11 isn’t a date.
There are always problems if you don’t use ISO 8601 format YYYY-MM-DD for dates. If you don’t see one currently, you will get some later.
Hi @emy1, taking advantage of @PKG’s tip, change the A370 formula to:
=SE(ÉANOBISSEXTO(A363);SOMARPRODUTO(MÊS(A$2:A$367)=MÊS(A369);$C$2:$C$367);SOMARPRODUTO(MÊS(A$2:A$366)=MÊS(A369);$C$2:$C$366))
=SUMIFS($D$31:$D$1030; $C$31:$C$1030;">="&$B$3 ; $C$31:$C$1030;"<="&$C$3)
It seems to be working well.
@Lupp
A date written with points between day, month and year like “30.11.2026” is the system used in my country.
That was bit the crucial point. 30.11 ia bor complete, so no date
@Wanderer
I formatted the cells to show only the day and the month; the year is pretty much pointless when the time range is short.