What is formula EDATE from Excel in Smartsheet

13

Answers

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

    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

  • MazU
    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
    MazU ✭✭✭✭✭

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

    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

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

    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

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

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

    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

  • Yanie
    Yanie ✭✭

    @Paul Newcome hi try the formula but its not working

  • 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

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

  • Yanie
    Yanie ✭✭
    edited 12/05/22
  • 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?

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

  • Yanie
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!