Need to add 6 months and 3 months to a date

Paul Newcome

@Deanna DeCoursey I want to make sure I am clear exactly what you are wanting...

You want to plug in a date. If that date is middle or beginning of the month, then then DAY for the new month would match the original month, but if the date is at the end of the month then we want the new day to be the end of the month and not potentially the beginning of the next month.

1 = 1

2 = 2

3 = 3

4 = 4

5 = 5

...

...

...

28 = 28

29 = 29 (or 28 for Feb in non-leap years)

30 = 30 (or 28/29 for Feb)

31 = 30 or 31 depending on the month (or 28/29 for Feb)

Deanna DeCoursey

@Paul Newcome Yes, that is exactly right. This is the formula I currently have...

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

Paul Newcome

@Deanna DeCoursey I think I am on to something with my testing as far as rolling the date back when it is pushed into the next month, but in the meantime I have been able to get the base formula more manageable. We are going to need it as short as possible because I feel like we are going to have to repeat it at least once more if not twice. Basically we nest the IF statements inside of the DATE functions instead of repeating the IFERROR(DATE(..........), DATE(..........)) multiple times.

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

Paul Newcome

@Deanna DeCoursey I think I may have it figured out. We had to use that base conversion formula a handful of times, so its good that we were able to shrink it down. Give this a try:

=IF(Frequency@row = "Weekly", [Previous Due Date]@row + 7, IF(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)))))) <> IF(Frequency@row = "Monthly", 1, IF(Frequency@row = "Quarterly", 3, IF(Frequency@row = "Semi-Annually (6 months", 6))), DATE(YEAR(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))))), 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))))), 1) - 1, 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))))))

Deanna DeCoursey

@Paul Newcome Thank you so much for looking into this! I tried plugging this in, but it is still coming out with some incorrect dates unfortunately. Yearly and weekly work just fine, but monthly, quarterly, and Semi-Annually are having difficulties coming over to the next year. It subtracts a month. Otherwise it works. Though, in some cases, Semi-Annually is only coming out as 5 months. See the screenshot below.

Paul Newcome

@Deanna DeCoursey It was working in my test sheet. Let me fiddle with it some more and get back to you.

Paul Newcome

Try this one. I think I found my mistake in that last one.

=IF(Frequency@row = "Weekly", [Previous Due Date]@row + 7, IF(ABS(MONTH([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))))) <> IF(Frequency@row = "Monthly", 11, IF(Frequency@row = "Quarterly", 9, IF(Frequency@row = "Semi-Annually (6 months", 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 Thank you! It still seems to have some incorrect dates. Specifically for Semi-Annually in all cases, and for monthly and quarterly in general when it is not bridging a new year. I included a screenshot below.

Both the day and the month it generates is having issues. Do you think a complimentary formula in a different column may assist in some way?

Paul Newcome

@Deanna DeCoursey Ugh. I know we can get this... Back to the drawing board (after a pot or two of coffee).

EDIT: I got the Semi-Annually piece figured out. It was a single missing closing parenthesis. Still working on the months that do not overlap into a new year.

Paul Newcome

@Deanna DeCoursey I think we may have it...

=IF(Frequency@row = "Weekly", [Previous Due Date]@row + 7, IF(VALUE(RIGHT(ABS(MONTH([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))))))) <> IF(Frequency@row = "Monthly", 1, IF(Frequency@row = "Quarterly", 9, IF(Frequency@row = "Semi-Annually (6 months)", 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 It is so close to working! There seems to be something wrong with the Quarterly formula when it stays in the same year... It generates the month before at the end of the month, unless the due date is at the end of the month already... It is so close!

Paul Newcome

@Deanna DeCoursey Ugh. I knew I was forgetting to test something. Sorry about that. Be right back...

Paul Newcome

@Deanna DeCoursey Are you ok with using a helper column?

Deanna DeCoursey

@Paul Newcome Yes! A helper column would be fine.

Paul Newcome

@Deanna DeCoursey Phew. Ok. That will make things a little easier. Let me get back on it.