# What is formula EDATE from Excel in Smartsheet

Hi,

I want to calculate {{last date}} * {{number of month}} = Edate

but in Smartsheet this formula from excel does not seem to exist ?

Halleluja,

I found the solution...;-)

your formula was totally right. I had to change the format of the column "last date" in date. It was "text/number".

As always the solution is directly there. Thank you for helping. ;-)😀

• ✭✭✭✭✭✭

A formula can be written to add/subtract months, days, or years, but I am not sure on how to convert it to a "serial number" once the new date is established.

• Good morning,

I wonder - in excel "edate" seems to be a simple formula. Why doesn´t it work in Smartsheet?

KR Joerg

• ✭✭✭✭✭✭

It may be how Smartsheet stores dates vs how Excel stores them. I can't say for sure. Do you know the logic Excel uses to generate the serial number?

• Dear Paul,

all I can say is that the cells use the following formula in excel "=EDATE (last date; number of month)

The cell than shows the next date for a next due maintenance.

I am pretty sure there is a way to replace that excel-formula in Smartsheet - I hope you find it ;-)) 😏

• ✭✭✭✭✭✭

There are quite a number of ways to produce a future date. The only part I am not sure about is that (based on the reading I did on the EDATE function) it produces a "serial number" that represents a date. If you only need the date, then that part should most definitely be doable. I don't have any experience with EDATE other than reading some documentation online.

If you just want to produce a date that is one month out from another date, you would use something along the lines of...

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

• Hi Paul,

I want to produce a date that is between 1 month and 36 month out from another date - depending what is written in

following cell [Turnus] // [letzte Wartung = last maintenance]

Therefore, I understood and translated your proposal this way

=date(DATE(YEAR([letzte Wartung]@2), MONTH([letzte Wartung]@2) + [Turnus]@2, DAY([letzte Wartung]@2)), DATE(YEAR([letzte Wartung]@2) + [Turnus]@2, 1, DAY([letzte Wartung]@2)))

Unfortunately, it does not work, yet - but why? 🙄

• ✭✭✭✭✭✭

The formula I provided can actually be broken down into two different parts.

Part 1:

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

This adds 1 to the month number and maintains the year and day. The catch is if the month is December. There is no month 13, so we use an IFERROR to default to

Part 2:

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

This adds 1 to the year and then uses 1 as the month and maintains the day.

.

.

To be able to use a variable out to cover multiple years... I am going to have to do some thinking and testing and get back to you.

• ✭✭✭✭✭✭

So I got a formula worked out, but for some reason my sheet is acting up. I am going to have to do some trouble shooting on it, and then I'll get back to you.

• ✭✭✭✭✭✭

Alright. There actually was not an issue. It was a misinterpretation of data on my part.

Plug this AS IS into your new date formula. I am assuming that [letzte Wartung] is your original date and Turnus is the number of months you want to move out.

=DATE(YEAR([letzte Wartung]@row) + ROUNDDOWN((MONTH([letzte Wartung]@row) + Turnus@row) / 12, 0) + IF(IF(MOD(MONTH([letzte Wartung]@row) + Turnus@row, 12) = 0, 12, MOD(MONTH([letzte Wartung]@row) + Turnus@row, 12)) = 12, -1), IF(MOD(MONTH([letzte Wartung]@row) + Turnus@row, 12) = 0, 12, MOD(MONTH([letzte Wartung]@row) + Turnus@row, 12)), DAY([letzte Wartung]@row))

• Good morning Paul,

>>"I am assuming that [letzte Wartung] is your original date and Turnus is the number of months you want to move out."

Yes, this is true ;-)

First, I tried your above formular (only replaced row with the row number - right?). It did not work. 😏

Then, I tried the following formular with little change (I put [ ] to "Turnus") -

=DATE(YEAR([letzte Wartung]@2) + ROUNDDOWN((MONTH([letzte Wartung]@2) + [Turnus]@2) / 12, 0) + IF(IF(MOD(MONTH([letzte Wartung]@2) + [Turnus]@2, 12) = 0, 12, MOD(MONTH([letzte Wartung]@2) + [Turnus]@2, 12)) = 12, -1), IF(MOD(MONTH([letzte Wartung]@2) + [Turnus]@2, 12) = 0, 12, MOD(MONTH([letzte Wartung]@2) + [Turnus]@2, 12)), DAY([letzte Wartung]@2))

Still, it does not work. Any idea left ? 😪

• the result is still "unparseable" - see attached screenshot.

• ✭✭✭✭✭✭

That's very odd.

When I built the formula, I built it in a sheet and even changed the column names to match yours to ensure the formula I provided would not need editing. The only difference is the commas vs semi-colons in between sections which varies by region. I know my region uses commas.

Halleluja,

I found the solution...;-)

your formula was totally right. I had to change the format of the column "last date" in date. It was "text/number".

As always the solution is directly there. Thank you for helping. ;-)😀

• ✭✭✭✭✭✭

I didn't even think of that. Usually a column type issue throws a different error. Good catch.

Happy to help! 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!