Add years of one column to date in another formula?

Mark Muenzen

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

Mark Muenzen

.

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.

Mark Muenzen

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

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

Mark Muenzen

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

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?

Mark Muenzen

[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

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

Mark Muenzen

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

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

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.

Mark Muenzen

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

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

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

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