date function in complex syntax

Question

Hi,

i have got a formula that I cannot decode.

Reference cell:

Input!$B$7 = 15/03/2024

Main formula:

=IF(OR(MONTH(Input!$B$7)<3,MONTH(Input!$B$7)>9),DATE(YEAR(Input!$B$7)+(MONTH(Input!$B$7)>3),2,28),DATE(YEAR(Input!$B$7)+(MONTH(Input!$B$7)>9),9,30))

my main question is in "DATE(YEAR(Input!$B$7)+(MONTH(Input!$B$7)>3),2,28)" and "DATE(YEAR(Input!$B$7)+(MONTH(Input!$B$7)>9),9,30)". when I break them down to "YEAR(Input!$B$7)" and "MONTH(Input!$B$7)>3", i get different answers.

Answer 1

The DATE function takes a year, month, and day. The expression  

DATE(YEAR(Input!$B$7)+(MONTH(Input!$B$7)>3),2,28)

computes the date of the "next" February 28 after the date in B7. In this context, "next" means February 28 of the same year as B7 when the date in B7 is in January, February, or March. If the date is in April or later, then "next" means February 28 of the next year.

The trick used is the fact that MONTH(B7)>3 evaluates to 0 for January, February, or March (and the date will be computed using the current year) and evaluates to 1 for a later date (and the date will be computed for current year + 1).

However, the IF function ensures that this expression is used only when the current month is January-February or October-December. Therefore, this DATE expression will not be evaluated for dates in March.

The second DATE expression does similar processing for September 30. Since it will only be used for dates that occur in March-August, the expression MONTH(B7)>9 will always evaluate to 0. As a result, any calculation of a September 30 date will always be for the current year.