Date calculations based on frequency

Whitney Johnston

I am struggling to get a formula to work. I am trying to write a formula that will calculate the next date a review will need to take place based on a manually entered date for when the last review was done and the frequency in which the review needs to take place for that row. The review frequencies are Monthly, Quarterly, Biannually and Annually (in that order).

I started with this and it worked wonderfully until any date went beyond the current year, in which case I would get an #invaled value error.

=IF([Review Frequency]@row = "Monthly", DATE(YEAR([Last Completed]@row), MONTH([Last Completed]@row) + 1, DAY([Last Completed]@row)), IF([Review Frequency]@row = "Quarterly", DATE(YEAR([Last Completed]@row), MONTH([Last Completed]@row) + 3, DAY([Last Completed]@row)), IF([Review Frequency]@row = "Biannually", DATE(YEAR([Last Completed]@row), MONTH([Last Completed]@row) + 6, DAY([Last Completed]@row)), IF([Review Frequency]@row = "Annually", DATE(YEAR([Last Completed]@row) + 1, MONTH([Last Completed]@row), DAY([Last Completed]@row))))))

I then added some IFERROR statements to try and correct the issue based on other chats and tutorials but it is giving me an #unparceable error now.

=IF([Review Frequency]@row = “Monthly”, DATE(YEAR([Last Completed]@row), MONTH([Last Completed]@row) + 1, DAY([Last Completed]@row)), IFERROR(DATE(YEAR([Last Completed]@row) + 1, MONTH([Last Completed]@row) - 11, DAY([Last Completed]@row)) – 1, IF([Review Frequency]@row = “Quarterly”, DATE(YEAR([Last Completed]@row), MONTH([Last Completed]@row) + 3, DAY([Last Completed]@row)) - 1, IFERROR(DATE(YEAR([Last Completed]@row) + 1, MONTH([Last Completed]@row) - 9, DAY([Last Completed]@row)) – 1, IF([Review Frequency]@row = “Biannually”, DATE(YEAR([Last Completed]@row), MONTH([Last Completed]@row) + 6, DAY([Last Completed]@row)) - 1, IFERROR(DATE(YEAR([Last Completed]@row) + 1, MONTH([Last Completed]@row) - 6, DAY([Last Completed]@row)) – 1, IF([Review Frequency]@row = “Annually”, DATE(YEAR([Last Completed]@row) + 1, MONTH([Last Completed]@row), DAY([Last Completed]@row)) - 1))))

All column names are spelled exactly, including capitalizations and spaces. I assume there is an issue with my order of operations or syntax since I have so many IFERROR statements. Any suggestions?

Whitney Johnston

After many hours of research, editing and re-editing to try to make this work, it turns out that I was MASSIVELY overcomplicating this formula. I was able to find a solution that seems to be working smoothly with no issues simply by transitioning from trying to manipulate the numbers in the date itself to simply adding the number of days to the overall date. Posting it here if anyone else needs this resource and to make their lives a little easier. Thank you to anyone that started to review this for me.

=IF([Review Frequency]@row = "Monthly", [Last Completed]@row + 30, IF([Review Frequency]@row = "Quarterly", [Last Completed]@row + 90, IF([Review Frequency]@row = "Biannually", [Last Completed]@row + 180, IF([Review Frequency]@row = "Annually", [Last Completed]@row + 365))))

Whitney Johnston

After many hours of research, editing and re-editing to try to make this work, it turns out that I was MASSIVELY overcomplicating this formula. I was able to find a solution that seems to be working smoothly with no issues simply by transitioning from trying to manipulate the numbers in the date itself to simply adding the number of days to the overall date. Posting it here if anyone else needs this resource and to make their lives a little easier. Thank you to anyone that started to review this for me.

=IF([Review Frequency]@row = "Monthly", [Last Completed]@row + 30, IF([Review Frequency]@row = "Quarterly", [Last Completed]@row + 90, IF([Review Frequency]@row = "Biannually", [Last Completed]@row + 180, IF([Review Frequency]@row = "Annually", [Last Completed]@row + 365))))

Genevieve P.

Hi @Whitney Johnston

Thank you for posting your solution! I'm glad to see you were able to create a succinct formula that executes exactly what you need.

Please do come back if you have any future formula questions 🙂