EDATE formula for End Date

Kelly Campbell

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

Michael Wilkesen

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

Michael Wilkesen

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

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

Kelly Campbell

@Jeff Reisman that worked! Thank you!

RobertZapata

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

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

RobertZapata

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

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

RobertZapata

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

@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, ""))

RobertZapata

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

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

RobertZapata

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

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