Having trouble returning valid dates
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???
Best Answer
-
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.
Answers
-
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)))), "")
-
Thanks Carson but this is still returning a #INVALID VALUE.
-
Is the column you are placing the formula into formatted as DATE?
-
Yes, it is a date.
-
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?
-
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?
-
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
-
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?
-
What is the exact formula you are now using?
-
I am using Carson's formula from above:
-
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.
-
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.
-
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?
-
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...
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!