What is formula EDATE from Excel in Smartsheet

Genevieve P.

Hi @Yanie

Can you try using this formula:

=IF(ISDATE([Date Hired]@row), "Yes Date", "Not a Date")

This will let us know if the value is being read correctly from the Date Hired column! If you get "Not a Date" then it's text. How is the date being added to your rows?

Can you also confirm that the column you're entering the formula into is also a Date column?

Yanie

same still invalid column value.

Paul Newcome

@Yanie Have you double checked that the column you are putting the formula in is also set as a date type column?

GarryD2

Paul,

I tried this equation:

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

Named my columns to match as I'm adding months onto a date, sometimes the months are 270, sometimes 60 etc. I made Turnus my months I want to add and Letzte Wartung my starting date Copied it exactly and pasted and I get unparseable. My letzte Wartung is a date column and so in is my final calculated date column. The months to add Turnus is just a number column.

Paul Newcome

@GarryD2 I don't see any syntax issues right off. Double check the column names are correct. Also, do you usually use the commas in other formulas, or do you typically use semi-colons?

GarryD2

Usually commas.

Lou Torres

I appreciate the formula. It seems close but the formula is also adjusting the year 4 years out. I just need the months to adjust. Could you please review what I need to change. I copied the formula exactly as it was written out. Thanks!

Genevieve P.

Hi @Lou Torres

Can you explain what it is you're looking to do? If you're just looking to add the number of months to a date, try this instead:

=IF(MONTH([Start Date]@row) + [Length in Months]@row > 12, DATE(YEAR([Start Date]@row) + 1, MONTH([Start Date]@row) - (12 - [Length in Months]@row), DAY([Start Date]@row)), DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + [Length in Months]@row, DAY([Start Date]@row)))

Cheers,

Genevieve

Lou Torres

Hi @Genevieve P.

Thank you for your help. The formula worked until it went past 20 months. I'm trying to automate the expiration date based on our client's agreement. The term could be past 12 months. So the formula will have to allow for years out as well. Months work best since we the terms could be 12, 18, 24, 36 months, etc.

I hope this helps!

Thank you!

Genevieve P.

Hi @Lou Torres

Do you have a set selection of timeframes? Years would be the easiest to add as we'd only need to +1 to the Year of the date. We could look for 12 months then just +1, or 24 and +2, or 36 and +3, and then build different formulas for the in-between dates.

Can you specify exactly what your criteria are? Or could it vary from anywhere between 1 month to 5 years?

Lou Torres

@Genevieve P. ,

Unfortunately the timeframes will vary. The terms are based on the client's previous firm so they're not set by us. But from what I've seen is normally, 6, 12, 18, 24, 36, or 48 months.

Thanks!

Paul Newcome

@Lou Torres What does this one do for you?

=IFERROR(IFERROR(DATE(YEAR([Original Date]@row) + ROUNDDOWN((MONTH([Original Date]@row) + [Number of Months]@row) / 12, 0) + IF(IF(MOD(MONTH([Original Date]@row) + [Number of Months]@row, 12) = 0, 12, MOD(MONTH([Original Date]@row) + [Number of Months]@row, 12)) = 12, -1) - IF(AND(ABS([Number of Months]@row) - MONTH([Original Date]@row) <> 12, [Number of Months]@row < 0, ABS([Number of Months]@row) > MONTH([Original Date]@row)), 1, 0), IF(MOD(MONTH([Original Date]@row) + [Number of Months]@row, 12) = 0, 12, MOD(MONTH([Original Date]@row) + [Number of Months]@row, 12)), DAY([Original Date]@row)), DATE(IF(MONTH([Original Date]@row) - ABS([Number of Months]@row) < 1, YEAR([Original Date]@row) - 1, YEAR([Original Date]@row)), IF(MONTH([Original Date]@row) - ABS([Number of Months]@row) < 1, MONTH([Original Date]@row) + (12 - ABS([Number of Months]@row)), MONTH([Original Date]@row) - ABS([Number of Months]@row)), DAY([Original Date]@row))), "")

Lou Torres

@Paul Newcome,

This works perfectly! Thank you sir, much appreciated!!