how to add 6 months in a date ?
Answers
-
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?
-
@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.).
-
@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?
-
@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), "")
-
Thanks so much @Paul Newcome ! Works perfectly!
-
@Cara Graf Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 201 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!