6 Month Anniversary Formula
Hello Everyone,
I am an excel wiz but I realize formula writing is totally different within smartsheet. I need help projecting 6 months in advance date. I used the following formula. =DATE(([Start Date]6) + 1),(([Start Date]6) - 6),([Start Date]6)). Any help would be great!
Best Answer
-
Try this:
=IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 6, DAY([Start Date]@row)), DATE(YEAR([Start Date]@row) + 1, MONTH([Start Date]@row) - 6, DAY([Start Date]@row)))
Answers
-
Try this:
=IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 6, DAY([Start Date]@row)), DATE(YEAR([Start Date]@row) + 1, MONTH([Start Date]@row) - 6, DAY([Start Date]@row)))
-
I appreciate your help Paul! I copied and pasted the formula but now get #invalid data type. I was getting this yesterday when attempting other formulas recommended within Community.
-
Is the [Start Date] column set as a date type column?
-
Thank you for helping me! I made it a Date column
-
Is it working now that you made the reference column a date type?
-
Thank you for helping me! I made it a Date column. It also looks like I have to expand the formula even more. If One of the columns contain text "W-2" or "Non-compete", instead of a date, I want it to return "N/A"
-
=IF(([3rd Party Hire]CONTAINS("W-2"), "N/A", (IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 5, DAY([Start Date]@row)), DATE(YEAR([Start Date]@row) + 1, MONTH([Start Date]@row) - 7, DAY([Start Date]@row))))
-
Actually, I got it!
=IF(OR([3rd Party Hire]@row = "W-2", [Start Date]@row = "TBD", [3rd Party Hire]@row = "Non-compete"), "N/A", (IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 5, DAY([Start Date]@row)), DATE(YEAR([Start Date]@row) + 1, MONTH([Start Date]@row) - 7, DAY([Start Date]@row)))))
-
Happy to help. 👍️
Glad you were able to get it sorted.
-
Hi @Paul Newcome,
I used your formula for adding 6 months to a date and modified it for a given duration of months, and it worked fine if the Start Date wasn't the 31st and I added a number of months that made the Finish Date a month with less than 31 days. This is my formula:
Finish Date = IFERROR(IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + [Duration Months]@row, DAY([Start Date]@row)), DATE(YEAR([Start Date]@row) + 1, MONTH([Start Date]@row) + [Duration Months]@row - 12, DAY([Start Date]@row))), "")
Works for: 31-Jan-2024 + 2 months = 31-Mar-2024
Doesn't work for 31-Jan-2024 + 3 months = 31-Apr-2024 (ERROR!)This would also be an issue with any such calculation with a starting date the 31st that has a Finish Date in Apr, Jun, Sep, Nov (30-day months) or starting on 29th/30th/31st and has a finish date in Feb (only 28/29 days).
Any suggestions how we could make the day either roll forward to be 1st of th next month, or keep it to the last possible day of the finish month?
Adrian Mandile
CHESS Consulting Australia - Smartsheet Solution Provider Gold Partner
Collaborative | Holistic | Effective | Systems | Solutions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!