Need to add 6 months and 3 months to a date

Paul Newcome

@Deanna DeCoursey Ok. Here we go. I think we may have it here...

Helper (text/number):

=ABS(MONTH([Previous Due Date]@row) - MONTH(IF(Frequency@row = "Weekly", [Previous Due Date]@row + 7, IFERROR(DATE(YEAR([Previous Due Date]@row) + IF(CONTAINS("by", Frequency@row), 1, 0), MONTH([Previous Due Date]@row) + IF(Frequency@row = "Monthly", 1, IF(Frequency@row = "Quarterly", 3, IF(Frequency@row = "Semi-Annually (6 months)", 6, 0))), DAY([Previous Due Date]@row)), DATE(YEAR([Previous Due Date]@row) + 1, MONTH([Previous Due Date]@row) - IF(Frequency@row = "Monthly", 11, IF(Frequency@row = "Quarterly", 9, IF(Frequency@row = "Semi-Annually (6 months)", 6, 0))), DAY([Previous Due Date]@row))))))

New Date (date):

=IF(Frequency@row = "Weekly", [Previous Due Date]@row + 7, IF(OR(AND(Frequency@row = "Monthly", Helper@row <> 1, Helper@row <> 11), AND(Frequency@row = "Quarterly", Helper@row <> 9, Helper@row <> 3), AND(Frequency@row = "Semi-Annually (6 months)", Helper@row <> 6)), DATE(YEAR(IFERROR(DATE(YEAR([Previous Due Date]@row) + IF(CONTAINS("by", Frequency@row), 1, 0), MONTH([Previous Due Date]@row) + IF(Frequency@row = "Monthly", 1, IF(Frequency@row = "Quarterly", 3, IF(Frequency@row = "Semi-Annually (6 months)", 6, 0))), DAY([Previous Due Date]@row)), DATE(YEAR([Previous Due Date]@row) + 1, MONTH([Previous Due Date]@row) - IF(Frequency@row = "Monthly", 11, IF(Frequency@row = "Quarterly", 9, IF(Frequency@row = "Semi-Annually (6 months)", 6, 0))), DAY([Previous Due Date]@row)))), MONTH(IFERROR(DATE(YEAR([Previous Due Date]@row) + IF(CONTAINS("by", Frequency@row), 1, 0), MONTH([Previous Due Date]@row) + IF(Frequency@row = "Monthly", 1, IF(Frequency@row = "Quarterly", 3, IF(Frequency@row = "Semi-Annually (6 months)", 6, 0))), DAY([Previous Due Date]@row)), DATE(YEAR([Previous Due Date]@row) + 1, MONTH([Previous Due Date]@row) - IF(Frequency@row = "Monthly", 11, IF(Frequency@row = "Quarterly", 9, IF(Frequency@row = "Semi-Annually (6 months)", 6, 0))), DAY([Previous Due Date]@row)))), 1) - 1, IFERROR(DATE(YEAR([Previous Due Date]@row) + IF(CONTAINS("by", Frequency@row), 1, 0), MONTH([Previous Due Date]@row) + IF(Frequency@row = "Monthly", 1, IF(Frequency@row = "Quarterly", 3, IF(Frequency@row = "Semi-Annually (6 months)", 6, 0))), DAY([Previous Due Date]@row)), DATE(YEAR([Previous Due Date]@row) + 1, MONTH([Previous Due Date]@row) - IF(Frequency@row = "Monthly", 11, IF(Frequency@row = "Quarterly", 9, IF(Frequency@row = "Semi-Annually (6 months)", 6, 0))), DAY([Previous Due Date]@row)))))

Deanna DeCoursey

@Paul Newcome That did it!! It works perfectly now with all my test dates! Thank you so much for all your help with this! Amazing!

Deanna DeCoursey

@Paul Newcome I actually have one final question. I am happy where the formula is now, but I am wondering if it is possible to make one final addition when accounting for dates at the end of month. So the sheet I have has an automation to set up reoccurring dates every month, quarterly, etc. How I have that set up is with this (amazing) formula and a "record current date" automation. Where on the due date, the automation is set up to place the current date in the "Previous Due Date" column, where the formula's can generate the new due date. The last issue is that if the due date is 28Feb2023, 30Apr2023, etc. what the due date is really supposed to do is be the last day of the month in this case. So monthly should be 31Mar2023 if the previous due date was 28Feb2023, but it is popping up as 28Mar2023. Eventually all the due dates will end up as 28MMMYYYY. Is there a way to get around that? Below is a screenshot.

Paul Newcome

@Deanna DeCoursey

Haha. The best answer I can give is "Maybe". There are some additional factors / "what ifs" to consider though. What if it is the 29th of January? This would push it to the last day of February. From there would it go to the last day of March since the previous was the last day of February, or would we continue with the 29th of March since the original date was the 29th of January?

I feel like this should be possible to create, but I also feel that we may need to work in at least one other indicator column to establish end of month vs day of month and possibly other indicators depending on all of the different possibilities you would need to accommodate.

I do see your concern that eventually every entry for 28th, 29th, 30th, and 31st would become the 28th as it passed through February, but we definitely want to make sure we are accounting for every possibility before we go making more changes.

If the only thing we need to worry about are those that are at the end of the month, then we can put in an indicator column. In this example I will use a checkbox column called [End of Month]. We can say that if the box is not checked then we want to output whatever the mega formula generates, but if the box is checked then we want to output the end of the month that is generated by the mega formula.

We would want to actually convert the [New Date] column into a helper column (so it can be hidden). I'll change that column name to [Helper Date], and then create a new [New Date] column with this in it:

=IF([End of Month]@row <> 1, [Date Helper]@row, IFERROR(DATE(YEAR([Date Helper]@row), MONTH([Date Helper]@row) + 1, 1), DATE(YEAR([Date Helper]@row) + 1, 1, 1)) - 1)

Deanna DeCoursey

@Paul Newcome Haha, I promise this is the last one! I see what you mean by the 29th January to 31st March issue going through February. That is not something I considered. I believe the only ones we need to worry about are the dates at the end of the month.

I went ahead and set up the columns as you mentioned, and that did the trick just fine! Much appreciated :)

Thanks again, Paul! You're help over this past week has ben incredible, and I feel comfortable presenting this sheet for my team to use.

You are an absolute genius.