Having trouble returning valid dates

SASardy

I am trying to track dates that are reliant upon a variable field. When the variable field is "x", the return in the second column should be the original date plus 1 year and 1 day. When the field is not "x", the return should be the original date plus 2 years and 1 day (edit for clarity: the first column should just be the original date plus 1 day). The problem I am running into is with any date that falls on the 31st of the month, it returns an #INVALID VALUE. Below are screenshots of a few dates I put in to test along with my formula. Can anyone help me solve this???

Paul Newcome

Your formula in your original post was already adding one year or two years to the date. Literally the only problem with the original formula was that your +1 was in the wrong spot. If you use the formula you provided in the screenshot of your original post and adjust where the +1 is, you will get the days and the years added as needed.

Just use your original formula and make the tweaks to the +1s that I suggested and you will have a formula that does what you want it to do including adding years.

Carson Penticuff

I think this will give you what you are looking for. Leap years make this much more complicated... if you ignore those, this could be much simpler.

=IF(ISDATE([New Count Start Date]@row), IF(Unattended@row = "x", IF(AND(MOD(YEAR([New Count Start Date]@row), 4) = 0, MONTH([New Count Start Date]@row) <= 2), [New Count Start Date]@row + 367, IF(AND(MOD(YEAR([New Count Start Date]@row), 4) = 3, MONTH([New Count Start Date]@row) >= 3), [New Count Start Date]@row + 367, [New Count Start Date]@row + 366)), IF(AND(MOD(YEAR([New Count Start Date]@row), 4) = 0, MONTH([New Count Start Date]@row) <= 2), [New Count Start Date]@row + 732, IF(MOD(YEAR([New Count Start Date]@row), 4) = 3, [New Count Start Date]@row + 732, IF(AND(MOD(YEAR([New Count Start Date]@row), 4) = 2, MONTH([New Count Start Date]@row) >= 3), [New Count Start Date]@row + 732, [New Count Start Date]@row + 731)))), "")

SASardy

Thanks Carson but this is still returning a #INVALID VALUE.

Carson Penticuff

Is the column you are placing the formula into formatted as DATE?

SASardy

Yes, it is a date.

Carson Penticuff

That is very strange, I have this working in a test sheet. Is it possible some of the dates in [New Count Start Date] are not formatted as text instead?

SASardy

I think this may be the issue. The column is set up as DATE but when I use the formula below, it is telling me it is returning a value that is not a date. Why is it doing that and how can I fix it?

Paul Newcome

Try your original formula, but instead of adding 1 to the DAY, add 1 to the DATE.

You have

DATE(yyyy, mm, dd + 1)

You want:

DATE(yyyy, mm, dd) + 1

SASardy

Thank you, Paul. This (mostly) worked.

First question, if I wanted to add a year rather than a day, how would I do that?

Second, as you can see below, February in a leap year does not work. 2/28/24 should return 3/1/26 in the last column. All the other cells are correct though. I assume SS just can't handle leap years correctly?

Paul Newcome

What is the exact formula you are now using?

SASardy

I am using Carson's formula from above:

Paul Newcome

Go back to the formula from your original post and then just make the little tweak I mentioned previously about moving the +1 from being plus one to the day number to plus one to the date.

SASardy

That change added 1 day. I need to add 1 day from the date in the first column to get the date in the second column. Then I need to add 1 year to the second column (or 1 day and 1 year to the first column) to get the date in the third column. And I need all results to be formatted as a date.

Paul Newcome

Your formula in your original post was adding a year (or two years depending on the Unattended@row field). What formula are you using now?

SASardy

Like I stated above, I am using Carson's formula to add the 1 or 2 years and your adjusted formula (the one where I moved the +1 to the outside of the parentheses) to add 1 day...

Paul Newcome

And like I stated above... You should be using the formula from your original post with only the tweaks I mentioned previously. Carson's formula is unnecessarily complex. Your original formula will work just fine so long as you move the +1 to add a day to the date function as opposed to adding one to the day number.