Date Formula Help

Question

I am trying to create a formula to pull a date into the Future Date column based on the report frequency and due date entered.

=IF([Report Frequency]@row = "Yearly", DATE(YEAR([Due Date]@row) + 1, MONTH([Due Date]@row), DAY([Due Date]@row)), "")

Above is what I entered, but it is throwing an invalid column value error. Can someone help?

Jennifer Kurtz · Accepted Answer

Good morning @Canteaug !

Your Future Date Column will need to be a Date column. Is it set as such? (You can check this in the column properties.)

Bira · Accepted Answer

Hello! @Canteaug

=IF([Report Frequency]@row = "Yearly", DATE(YEAR([Due date]@row) + 1, MONTH([Due date]@row), DAY([Due date]@row)), "")

Jennifer Kurtz · Accepted Answer

@Canteaug - Hm… I can't replicate your issue. I copied your formula into a test sheet, and the "Quarterly" instance looks just fine!

What happens exactly for you with regard to a "Quarterly" row? Are you receiving an error message? Do you have a date in the Due Date column for that row?

Bira · Accepted Answer

@Canteaug

@Jennifer Kurtz

Here is correct formula to handle:

If the month is 12 (December), adding 1 month means moving to January of the next year.

=IF(ISDATE([Due Date]@row), IF([Report Frequency]@row = "Monthly", IF(MONTH([Due Date]@row) = 12, DATE(YEAR([Due Date]@row) + 1, 1, DAY([Due Date]@row)), DATE(YEAR([Due Date]@row), MONTH([Due Date]@row) + 1, DAY([Due Date]@row))), IF([Report Frequency]@row = "Quarterly", IF(MONTH([Due Date]@row) = 10, DATE(YEAR([Due Date]@row) + 1, 1, DAY([Due Date]@row)), IF(MONTH([Due Date]@row) = 11, DATE(YEAR([Due Date]@row) + 1, 2, DAY([Due Date]@row)), IF(MONTH([Due Date]@row) = 12, DATE(YEAR([Due Date]@row) + 1, 3, DAY([Due Date]@row)), DATE(YEAR([Due Date]@row), MONTH([Due Date]@row) + 3, DAY([Due Date]@row))))), IF([Report Frequency]@row = "Yearly", DATE(YEAR([Due Date]@row) + 1, MONTH([Due Date]@row), DAY([Due Date]@row)), ""))), "")