What is formula EDATE from Excel in Smartsheet

124»

Answers

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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Yanie
    Yanie ✭✭

    same still invalid column value.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • GarryD2
    GarryD2 ✭✭✭
    edited 02/20/23

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • GarryD2
    GarryD2 ✭✭✭

    Usually commas.

  • 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!


  • 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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • 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!

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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • @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
    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))), "")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • @Paul Newcome,

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!