how to add 6 months in a date ?

Cara Graf

Additional question for you, @Paul Newcome

The formula you mentioned above works great. Would there be a way to alter the formula to always end on a Friday? For example, I start courses on a Monday and the students have 6 months to complete. We always end on a Friday. So, instead of marking 6 months to the date, is there a way to do the Friday after the 6 month date, if it doesn't land on a Friday by chance?

Paul Newcome

@Cara Graf You would use something like this:

=date_generating_formula + IF(WEEKDAY(date_generating_formula) <= 6, (6 - WEEKDAY(date_generating_formula)), 6)

You can modify this for any day of the week by changing each of the 6s to whichever weekday number the day is you wanted to generate (2 for Monday, 3 for Tuesday, etc.).

Cara Graf

@Paul Newcome My current formula is: =IFERROR(IFERROR(DATE(YEAR([Course Start Date]@row), MONTH([Course Start Date]@row) + 6, DAY([Course Start Date]@row)), DATE(YEAR([Course Start Date]@row) + 1, MONTH([Course Start Date]@row) - 6, DAY([Course Start Date]@row))), "")

With your suggestion, I would enter this entire formula where you've listed date_generating_formula? I've attempted to input this several ways and always get a syntax error or invalid data type. Any advice?

Paul Newcome

@Cara Graf Yes. You would drop your existing formula into each of the three places mentioned (excluding your very first equal symbol).

There may be an issue when there is no date because your original formula is outputting a blank with the final IFERROR. In that case you would need to wrap this new formula in another IFERROR to also output a blank.

=date_generating_formula + IF(WEEKDAY(date_generating_formula) <= 6, (6 - WEEKDAY(date_generating_formula)), 6)

=IFERROR(IFERROR(DATE(YEAR([Course Start Date]@row), MONTH([Course Start Date]@row) + 6, DAY([Course Start Date]@row)), DATE(YEAR([Course Start Date]@row) + 1, MONTH([Course Start Date]@row) - 6, DAY([Course Start Date]@row))), "") + IF(WEEKDAY(IFERROR(IFERROR(DATE(YEAR([Course Start Date]@row), MONTH([Course Start Date]@row) + 6, DAY([Course Start Date]@row)), DATE(YEAR([Course Start Date]@row) + 1, MONTH([Course Start Date]@row) - 6, DAY([Course Start Date]@row))), "")) <= 6, (6 - WEEKDAY(IFERROR(IFERROR(DATE(YEAR([Course Start Date]@row), MONTH([Course Start Date]@row) + 6, DAY([Course Start Date]@row)), DATE(YEAR([Course Start Date]@row) + 1, MONTH([Course Start Date]@row) - 6, DAY([Course Start Date]@row))), ""))), 6)

=IFERROR(IFERROR(IFERROR(DATE(YEAR([Course Start Date]@row), MONTH([Course Start Date]@row) + 6, DAY([Course Start Date]@row)), DATE(YEAR([Course Start Date]@row) + 1, MONTH([Course Start Date]@row) - 6, DAY([Course Start Date]@row))), "") + IF(WEEKDAY(IFERROR(IFERROR(DATE(YEAR([Course Start Date]@row), MONTH([Course Start Date]@row) + 6, DAY([Course Start Date]@row)), DATE(YEAR([Course Start Date]@row) + 1, MONTH([Course Start Date]@row) - 6, DAY([Course Start Date]@row))), "")) <= 6, (6 - WEEKDAY(IFERROR(IFERROR(DATE(YEAR([Course Start Date]@row), MONTH([Course Start Date]@row) + 6, DAY([Course Start Date]@row)), DATE(YEAR([Course Start Date]@row) + 1, MONTH([Course Start Date]@row) - 6, DAY([Course Start Date]@row))), ""))), 6), "")

Cara Graf

Thanks so much @Paul Newcome ! Works perfectly!

Paul Newcome

@Cara Graf Happy to help. 👍️

Pestomania

I hope Smartsheet finds a better solution for this. I wanted to add different month types based on a separate field, so I put this together since it cannot to the next year without issue and then you have to address how many months to subtract out of the year.

DATE(YEAR([Last Compliance Date]@row) + 1, MONTH([Last Compliance Date]@row) - (12 - VALUE(LEFT([Compliance Time Frame]@row, (FIND(" month(s)", [Compliance Time Frame]@row) - 1)))), DAY([Last Compliance Date]@row))