What is formula EDATE from Excel in Smartsheet

Joerg Schmidt

Hi,

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

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

Paul Newcome

No. You should be able to copy/paste my formula EXACTLY AS IS.

@row simply tells the formula to look at the specified column in the same row as the formula. So if the formula is on row 1, it will look at the column in row 1. If it is on row 2, it will look at row 2, so on and so forth. It replaces the row number.

As for the [Square Brackets]... The only time you need them in a formula is if your column name contains a space, number, or special character. If there are only alphabetical characters and no spaces in your column name, then you do not need the square brackets. You will notice that if you just type in an equals sign like you are going to start a formula then click any cell within the column. The square brackets will be generated if needed such as for [letzte Wartung] and they will not be generated when you click in the Turnus column.

You CAN type them in for [Turnus], but once we get the formula to actually work you will notice that Smartsheet will automatically remove them anyway.

Joerg Schmidt

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. ;-)😀

Paul Newcome

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.

Joerg Schmidt

Good morning,

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

KR Joerg

Paul Newcome

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?

Joerg Schmidt

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 ;-)) 😏

Paul Newcome

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

Joerg Schmidt

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? 🙄

Paul Newcome

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.

Paul Newcome

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.

Paul Newcome

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

Joerg Schmidt

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 ? 😪

Paul Newcome

No. You should be able to copy/paste my formula EXACTLY AS IS.

@row simply tells the formula to look at the specified column in the same row as the formula. So if the formula is on row 1, it will look at the column in row 1. If it is on row 2, it will look at row 2, so on and so forth. It replaces the row number.

As for the [Square Brackets]... The only time you need them in a formula is if your column name contains a space, number, or special character. If there are only alphabetical characters and no spaces in your column name, then you do not need the square brackets. You will notice that if you just type in an equals sign like you are going to start a formula then click any cell within the column. The square brackets will be generated if needed such as for [letzte Wartung] and they will not be generated when you click in the Turnus column.

You CAN type them in for [Turnus], but once we get the formula to actually work you will notice that Smartsheet will automatically remove them anyway.

Joerg Schmidt

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

2020-03-09 Smartsheet3.png

Paul Newcome

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.

Joerg Schmidt

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. ;-)😀

Paul Newcome

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

Happy to help! 👍️