clarify the complex date function

Question

hi,

i cannot make sense of this function, could you please clarify it to me:

=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))

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

what does "MONTH(Input!$B$7)>3)" mean?

Answer 1

Examine the pieces. The IF function requires three arguments:

• an expression to be evaluated as true or false.
• an expression to be evaluated and returned if the first expression evaluates to true.
• an expression to be evaluated and returned if the first expression evaluates to false.

The first expression is the OR function. It takes multiple argument and evaluates to true if any of the arguments evaluated to true. In this case, there are two arguments and both check the value of month.

The other two expression involve the DATE function. The first evaluates to 28 Feb in either the same year as B7 or the next, depending on whether the month in B7 is before April or not. The second does similarly for 30 Sep.

Answer 2

Does it work as expected?

MONTH(Input!$B$7)>3 is 1 if the month is greater than 3, else it is 0. Therefore the year is increased by one if the month is April, May, etc.

MONTH(Input!$B$7)>9 compares the month with September.