Add years of one column to date in another formula?

Mark Muenzen
Mark Muenzen ✭✭
edited 05/17/23 in Formulas and Functions

Hello -

I'm trying to add the years of one column to the date in another but I keep getting different errors. Here is where I'm at, any suggestions?

Most recent audit date - Column Type = Date

Estimated roof life remaining - Column Type = Text/number. There is a formula in this cell to create the data

Date of Replacement Est from most recent survey - Column Type = Date


«1

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Looks like your parenthesis are off is all.


    Move the closing parenthesis after the second cell reference to after the third cell reference.

    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

  • Hi Paul -

    Still getting an Invalid Data Type error. Here is what I am trying now

    =DATE(YEAR([Most Recent Audit Date]@row) + [Estimated Roof Life Remaining - Years]@row, MONTH([Most Recent Audit Date]@row, DAY([Most Recent Audit Date]@row)))

    Thoughts?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You moved the closing parenthesis to the wrong location. Take one off of the end and use it to close out the MONTH function.

    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

  • Still getting Invalid Data type

    =DATE(YEAR([Most Recent Audit Date]@row) + [Estimated Roof Life Remaining - Years]@row, MONTH([Most Recent Audit Date]@row), DAY([Most Recent Audit Date]@row))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Is the [Most Recent Audit Date] column set as a date type column? If so, how is it being populated?


    Is the [Estimated Roof Life Remaining - Years] column set as a text/number column? If so, how is it being populated?

    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

  • [Most recent Audit Date] column type is Date and the check box for "restricted to dates only" is checked. The date is manually entered.

    {Estimated Roof Life Remaining] column type is Text/Number. Formula is:

    =IF([Cumulative Rating]@row = "95", "10 Years", IF([Cumulative Rating]@row = "85", "8 Years", IF([Cumulative Rating]@row = "75", "5", IF([Cumulative Rating]@row = "65", "2 Years", IF([Cumulative Rating]@row <= "55", "Replacement Overdue ")))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Oh. Well there's the problem. The formula you have posted above is outputting text strings and not numbers. We can accommodate that no problem with the exception of one question...


    What would be the expected output for the date if the "number of years" is "Replacement Overdue"?

    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

  • Ideally the date would be effectively static. IE if the target repalcement is 11/29/2028, and we pass that date, the target replacement should still be shown 11/29/28.

    However, I am more than willing to change some outputs based on the limitations of the formulas so that we don't make ourselves crazy.

    If "Replacement Overdue" needs to just be a "0" or become a negative number to follow numerical standards I'd be 100% ok with it

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The only change you would need to make to your IF formula would be to make the year outputs consistent.

    =IF([Cumulative Rating]@row = "95", "10 Years", IF([Cumulative Rating]@row = "85", "8 Years", IF([Cumulative Rating]@row = "75", "5 Years", IF([Cumulative Rating]@row = "65", "2 Years", IF([Cumulative Rating]@row <= "55", "Replacement Overdue")))))


    From there we would use something like this:

    =DATE(YEAR([Most Recent Audit Date]@row) + IF([Estimated Roof Life Remaining - Years]@row = "Replacement Overdue", 0, VALUE(LEFT([Estimated Roof Life Remaining - Years]@row, FIND(" ", [Estimated Roof Life Remaining - Years]@row) - 1))), MONTH([Most Recent Audit Date]@row), DAY([Most Recent Audit 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

  • sandra.love
    sandra.love ✭✭✭

    I have found this thread while looking for how I can add a numerical value in one column to a date in another column (add number of days). I tried the basic formula of date column + value column but instead of adding the value to the days, it just adds it as a number at the end of the date. I thought maybe I could use a simplified version of the formula you provided above but it still doesn't work. I can get it to work if I manually put a value in the formula but the value is different depending on payment terms. Can you help?

    =DATE(MONTH([Invoice Date]@row), DAY([Invoice Date]@row) + days@row, YEAR([Invoice Date]@row))


    My ultimate goal is when an invoice date (red) is populated, I want the cash discount days (purple) to add to the invoice date to tell me when the cash discount expires.


  • Hi @sandra.love -

    Unfortunately, I was never able to get this formula to work correctly and needed to move on to other issues. Perhaps @Paul Newcome could assist?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @sandra.love You should be able to just add the days to the dates.

    =[Date Column]@row + [# of Days Column]@row


    If it is adding the number of days to the end of the date as a string, then we have a text string somewhere in the mix. Have you ensured that your date column is in fact set as a date type column and is also storing actual dates? Same for your number of days column. Make sure that is a text/number column and is being populated with numbers as opposed to text strings that just look like numbers.


    You are also going to want to make sure that your formula to output the final date is going in a date type column as well.

    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

  • sandra.love
    sandra.love ✭✭✭

    Hi @Paul Newcome

    That is the fomula I am unable to get to work, it adds the number of days as 2 digits at the end of the year


    So my invoice date column and my cash discount exp date column are both date type columns (see below) the invoice column is manually populated by a user. the cash discount exp date column is the one I want my formula populating.

    The Cash Discount Days column is pulling in a number from another sheet. Below is that formula. I also included an image of the other sheet to show how that number is populated.



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @sandra.love Wrap your MID formula in the last screenshot in a VALUE function.


    The MID function outputs a text value, and that's where the issue is coming from. Wrapping it in a VALUE function will convert it into a numeric value which in turn can be added to a date the way you want it to.


    =VALUE(MID(..........))

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!