How do you make IF and IF(AND statements work together

mleesc

I am trying to populate a cell based on multiple date criteria. I am currently trying to use the below formula but I am getting an unparseable error

=IF([Date Submitted]@row > DATE(2024, 9, 30), 25, ""), IF(AND([Date Submitted]@row > DATE(2023, 9, 30), [Date Submitted]@row < DATE(2024, 10, 1)), 24, "")

Kelly P.

@mleesc What are you trying to accomplish? Is the column with the formula supposed to populate with the values 25 and 24? If so, try this:

=IF([Date Submitted]@row > DATE(2024, 9, 30), 25, IF(AND([Date Submitted]@row > DATE(2023, 9, 30), [Date Submitted]@row < DATE(2024, 10, 1)), 24, ""))

Hope this helps!

mleesc

@Kelly P. I am trying to populate the cell with 25 if Date Submitted cell on the row is greater than 9/30/2024 and populate cell with 24 if the Date Submitted cell on the row is between 9/30/2023 and 10/1/2024.

The formula you provided does not error but only shows a blank cell

Kelly P.

@mleesc That's strange; it works for me! Can you provide screenshots?

mleesc

@Kelly P. I agree it should work.

This is the date submitted cell

This is the formula I entered based on you response

Kelly P.

@mleesc The Date Submitted value in your screenshot produces a blank because it's in a time period not addressed by the formula.

mleesc

@Kelly P. Do you know of a function that will address the time issue

Kelly P.

@mleesc Try this:

=IF(YEARDAY([Date Submitted]@row) > 274, RIGHT(YEAR([Date Submitted]@row) + 1, 2), IF(YEARDAY([Date Submitted]@row) <= 274, RIGHT(YEAR([Date Submitted]@row), 2)))

mleesc

@Kelly P. That works great can you explain that formula to me

Kelly P.

@mleesc Sure! The YEARDAY function assigns a number, 1-365, that represents the day in the year for the Date Submitted. Your cutover is 9/30 which is day number 274. So the formula says, if the YEARDAY value is more than 274 (a date greater that 9/30), then get the year from the Date Submitted, add one to it, and then pull just the last two numbers of that year (because the year is really a four digit number), but if the YEARDAY is less than or equal to 274, then pull the last two digits of the Date Submitted year.

mleesc

@Kelly P. Thank you very much

Kelly P.

@mleesc Happy to help! 😊