What is formula EDATE from Excel in Smartsheet

Melanie Pink

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?

Paul Newcome

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

MazU

@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

please can you assist me

MazU

https://community.smartsheet.com/discussion/comment/312872#Comment_312872

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

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

Paul Newcome

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

PEARCEMI

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?

Paul Newcome

@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

PEARCEMI

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

Yanie

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)

Paul Newcome

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

Yanie

@Paul Newcome hi try the formula but its not working

Genevieve P.

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

Yanie

@Genevieve P. this is the result.

Genevieve P.

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?

Yanie

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.