Formula to calculate a future date on a specific day (monthly and quarterly)

Natalia Kataoka

I have tasks that need to be completed monthly and quarterly. The due date of these tasks falls on specific days of the month, either the 5th, 14th, or 15th.

I have a formula that calculates the Next Date based on a Due column and a Frequency of Submittals column. Next Date and Due are date columns. My basic formula to calculate a date + 7/30/120 days works fine, but now I need to add a condition that says if the Date of Submittals column is not blank, to calculate, for example, the 15th day of the following month OR the 5th day in the next quarter. However, my quarters are not fiscal year quarters, just Due + 3 months. What I am really struggling with is the calculation for the quarter dates, plus how to get these to span multiple years.

Current formula I am using:

=IF(Final@row = 1, "", IF([Frequency of Submittals]@row = "Monthly", Due@row + 30, IF([Frequency of Submittals]@row = "Weekly", Due@row + 7, IF([Frequency of Submittals]@row = "Quarterly", Due@row + 120, ""))))

In the first row of my screenshot, I need the Next Date to read 10/5/25.

AdamSYNH

Hi @Natalia Kataoka,

Making use of the MONTH, YEAR and DATE formulas you can add logic to account for a variable number of days between the 5th day of each quarter and entering a new calendar year. Try the below formula:

=IF(Final@row = 1, "", IF([Frequency of Submittals]@row = "Monthly", Due@row + 30, IF([Frequency of Submittals]@row = "Weekly", Due@row + 7, IF([Frequency of Submittals]@row = "Quarterly", DATE(YEAR(Due@row + 120), IF(MONTH(Due@row ) = 10, 1, MONTH(Due@row ) + 3), [Date of Submittals]@row), ""))))

AdamSYNH

Hi @Natalia Kataoka,

Making use of the MONTH, YEAR and DATE formulas you can add logic to account for a variable number of days between the 5th day of each quarter and entering a new calendar year. Try the below formula:

=IF(Final@row = 1, "", IF([Frequency of Submittals]@row = "Monthly", Due@row + 30, IF([Frequency of Submittals]@row = "Weekly", Due@row + 7, IF([Frequency of Submittals]@row = "Quarterly", DATE(YEAR(Due@row + 120), IF(MONTH(Due@row ) = 10, 1, MONTH(Due@row ) + 3), [Date of Submittals]@row), ""))))