Share via

clarify the complex date function

David Polsangi 0 Reputation points
2024-07-14T06:13:03.51+00:00

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?

Microsoft 365
Microsoft 365
Formerly Office 365, is a line of subscription services offered by Microsoft which adds to and includes the Microsoft Office product line.
4,347 questions
Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,687 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Viorel 114.7K Reputation points
    2024-07-14T06:47:16.19+00:00

    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.

    0 comments
  2. Barry Schwarz 2,511 Reputation points
    2024-07-14T07:13:15.7333333+00:00

    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.

    0 comments