# What is formula EDATE from Excel in Smartsheet

Options

• Options

I want to use the edate function from excel in smartsheet. I want to take my current date ("date of execution" column) and add 14 months (my "duration") column. in excel it would simply look like this =EDATE(D5,E5)

why wont this work in smartsheet?

• ✭✭✭✭✭✭
Options

@Melanie Pink Adding 14 months would look something like this...

=IFERROR(DATE(YEAR([Date Column]@row) + 1, MONTH([Date Column]@row) + 2, DAY([Date Column]@row)), DATE(YEAR([Date Column]@row) + 2, MONTH([Date Column]@row) - 10, DAY([Date Column]@row)))

• ✭✭✭✭✭
Options

@Paul Newcome Hi Paul,

hoping you can help me

=IFERROR(DATE(YEAR([Date Column]@row), MONTH([Date Column]@row) + 1, DAY([Date Column]@row)),

DATE(YEAR([Date Column]@row) + 1, 1, DAY([Date Column]@row)))

I'm trying to use this formula but can't get the second part of the formula working.

Im using "-3" to work out a within 3 months range for this date column

how do i configure the second part of the formula to counter the invalid value fields

• ✭✭✭✭✭
Options

@Paul Newcome Hi, why did you put -10 after month section please

I am trying to understand the function here but not really sure

• ✭✭✭✭✭✭
Options

@Maz Uddin Basically the first DATE function adds the number of months. When that number goes beyond 12 it becomes as issue as there are no month numbers greater than 12. That is where the second DATE function comes into play. We can take 12 of those months and move then over to the the YEAR + 1 then add in the remaining months.

In your particular case you are wanting to use 3 months. That means the second date function would use 9 since 12 - 3 = 9.

• ✭✭
Options

Hello @Paul Newcome. I am trying to calculate future month due dates based on a past service date. All of these dates are the last day of the month and am calculating out 3,6,12 months. I have looked at many of these threads where you have offered solutions and I have gotten those to work for my purpose except for the fact that when the total days in the month differ, the formulas round to the first day of the next month (e.g. 3 months from Aug 31 = Dec 1). Have you or others figured out a way to prevent this using formulas?

• ✭✭✭✭✭✭
Options

@PEARCEMI You would want to add one more month to the total and set the day portion of the DATE function to 1 (1st day of the next month). You would then subtract 1 day from this.

=DATE(year_portion, month_portion + 1, 1) - 1

• ✭✭
Options

Thanks @Paul Newcome. I used your strategy to improve several formulas for day, month and year projections.

• ✭✭
Options

Hi, I have an employee data sheet with columns, Date Hired, and Years of service. How do I calculate their next Years of Service anniversary date which is acknowledged every 5 years (5, 10, 15, 20 etc)

• ✭✭✭✭✭✭
Options

@Yanie Try this:

=DATE(YEAR([Date Hired]@row) + CEILING(YEAR(TODAY()) - YEAR([Date Hired]@row), 5) + IF(TODAY() > DATE(YEAR([Date Hired]@row) + CEILING(YEAR(TODAY()) - YEAR([Date Hired]@row), 5), MONTH([Date Hired]@row), DAY([Date Hired]@row)), 5, 0), MONTH([Date Hired]@row), DAY([Date Hired]@row))

• ✭✭
Options

@Paul Newcome hi try the formula but its not working

Options

Hi @Yanie

It would be helpful to have more information in order to help - what didn't work about the formula? Are you receiving an error or an incorrect result? Can you post a screen capture of it in your sheet (but block out sensitive data).

Thanks! Genevieve

• ✭✭
edited 12/05/22
Options

@Genevieve P. this is the result.

Options

Hi @Yanie

That formula indicates that the cell it's looking at (the one that says 19/05/21) does not contain a Date value.

Can you check to see if your "Date Hired" column is a Date column? If it is, then how is the information being input into that cell?

• ✭✭
Options

Hi @Genevieve P. I try it both "Date Hired" column is a Date column and not. same INVALID DATA TYPE. I just want a formula that can show who will turn 5,10,15 years in the service.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!