EDATE formula for End Date
![Kelly Campbell](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
I'm trying to find the correct EDATE replacement formula for finding the Contract End Date. I've been playing around for about 30 minutes and decided to ask here as I'm unable to find it myself.
Activation Date + Term = Contract End Date
ex. 2017-10-04 = 3 = 2020-10-04
Best Answer
-
Kelly, Try this solution:
=DATE(YEAR([Activation Date]@row) + [Term (Year)]@row, MONTH([Activation Date]@row), DAY([Activation Date]@row))
I hope this helps!
Michael
Answers
-
Kelly, Try this solution:
=DATE(YEAR([Activation Date]@row) + [Term (Year)]@row, MONTH([Activation Date]@row), DAY([Activation Date]@row))
I hope this helps!
Michael
-
If your Activation Date column is a Date-type column and those are valid date values, you can try this:
=DATE(YEAR([Activation Date]@row) + 3, MONTH([Activation Date]@row), DAY([Activation Date]@row))
This constructs a new date value based on the individual date elements from the value in Activation Date.
If Activation Date is a Text/Number value, you could try this:
=(VALUE(LEFT([Activation Date]@row, 4)) + 3) + MID([Activation Date]@row, 5, 6)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman that worked! Thank you!
-
HI @Jeff Reisman,
Appreciate to help out as i wanted to have a formula that provides a date after 12 Month or 6 Months?
-
@RobertZapata The easiest way to do that is to add 182 days to the date for 6 months, and 365 days for 12 months:
=([Current Re-Evaluation]@row + 182)
If you need that 6 or 12 month date to fall on a business day, it gets more complicated, but still possible.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hi @Jeff Reisman,
Thanks and i have thought of that but i was thinking more on IF formula to be combined. As that would automate when they choose the grade (grade 1 = 6 months & grade 2 - 12 months). When they upload the current evaluation date then the next evalution date will show up.
Really hope you help me on this as i am not concern so much if business day or not as this is our tracker only.
-
@RobertZapata Something like this. You'll need to verify the color coding on the parentheses once you've written it in Smartsheet:
=IF(AND(Grade@row =1, ISDATE([Current Re-Evaluation]@row)),
(IF(OR(WEEKDAY([Current Re-Evaluation]@row + 182) = 7, WEEKDAY([Current Re-Evaluation]@row + 182) = 1), ([Current Re-Evaluation]@row + 184), ([Current Re-Evaluation]@row + 182)),
IF(AND(Grade@row =2, ISDATE([Current Re-Evaluation]@row)),
(IF(OR(WEEKDAY([Current Re-Evaluation]@row + 365) = 7, WEEKDAY([Current Re-Evaluation]@row + 365) = 1), ([Current Re-Evaluation]@row + 367), ([Current Re-Evaluation]@row + 365)), ""))
In English:
If the Grade = 1 and there's a date value in Current Re-Evaluation, add 182 days to the Current Re-Evaluation date, unless that falls on a Saturday or Sunday, in which case add 184 days.
If the Grade = 2 and there's a date value in Current Re-Evaluation, add 365 days to the Current Re-Evaluation date, unless that falls on a Saturday or Sunday, in which case add 367 days.
Otherwise, leave the value blank.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hi @Jeff Reisman,
Thanks but it was extremely long and would be able to provide an simplier one? As can't it count only 6 months for grade 1 and 12 months for grade without taking in account weekdays and weekends? As that part does not matter to my application.
-
=IF(AND(Grade@row =1, ISDATE([Current Re-Evaluation]@row)), [Current Re-Evaluation]@row + 182, IF(AND(Grade@row =2, ISDATE([Current Re-Evaluation]@row)), [Current Re-Evaluation]@row + 365, ""))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thanks and tried the formula and i am not sure where i got the lines wrong here. Appreciate to help?
-
When troubleshooting formulas, always include the error message the formula generates. Those mean something, and help guide you towards solving the problem. You can see what those messages mean here.
If the first open parentheses is not the same color as the last close parentheses, then you know you've likely got a syntax problem.
In this case, I think your issue is this parentheses here:
Remove it and see if your first and last parentheses both turn blue.
Also, as a best practice, I always recommend using ALL CAPS for your functions (IF, AND, ISDATE, etc.) I don't think all caps are still required, but it does make it easy to see which things are functions when working on formulas.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hi @Jeff Reisman,
Tried the same and it is the same issue.
Really giving me some hardships and hope i can get this working.
-
@RobertZapata If you're getting that #INVALID COLUMN VALUE error, it's because you're trying to use a function that works for a particular column type on a column that is not compatible. So I'm going to ask: are all your columns with dates in them formatted as DATE columns? To use something like ISDATE, and to add days to date values, all the columns involved need to be DATE columns.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 352 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 135 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!