Date Formula Help

Canteaug

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

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

Hello! @Canteaug

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

Jennifer Kurtz

@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

@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)), ""))), "")

Jennifer Kurtz

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

Hello! @Canteaug

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

Canteaug

Thank you, @Jennifer Kurtz I updated it to a date column and it worked. However, I added monthly and quarterly to the formula: monthly and yearly work but not quarterly. Do you have any ideas why the quarterly piece is not pulling?

=IF(ISDATE([Due Date]@row), IF([Report Frequency]@row = "Monthly", DATE(YEAR([Due Date]@row), MONTH([Due Date]@row) + 1, DAY([Due Date]@row)), IF([Report Frequency]@row = "Quarterly", 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)), ""))), "")

Jennifer Kurtz

@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?

Canteaug

@Jennifer Kurtz Yes, it has a date and still doesn't seem to work. I made another column just with the quarterly formula and it also works there. It is throwing an invalid error message. It seems to only throw an error when I enter the date of 12/31/24. It works for the other dates entered for Quarterly.

Jennifer Kurtz

@Canteaug - Oh! Gosh, my apologies. I should have thought of this. Your Quarterly part of the formula is going to hit an issue any time the date is between 9/30 & 12/31 because you will be spanning years.

I'll see if I can figure out a different way to do this — but someone else may chime in faster who has experience doing this sort of thing!

Bira

@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)), ""))), "")

Canteaug

Wow! Thank you both so much! @Bira @Jennifer Kurtz