EDATE formula for End Date

Kelly Campbell
edited 01/30/23 in Formulas and Functions

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

Answers

  • Michael Wilkesen
    Michael Wilkesen ✭✭✭✭
    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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @Kelly Campbell

    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!

  • HI @Jeff Reisman,

    Appreciate to help out as i wanted to have a formula that provides a date after 12 Month or 6 Months?


  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @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.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @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.



  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @RobertZapata

    =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!

  • Hi @Jeff Reisman

    Thanks and tried the formula and i am not sure where i got the lines wrong here. Appreciate to help?


  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 10/26/23

    @RobertZapata

    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.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!