Comparing Dates and Ignoring Year
Answers
-
That's kinda tricky what you are asking for, it seems easy at first sight, but it requires a bit of modifications to dobefore comparing the two cells.
We could go with some helper columns, but we'd go straight for it.
Your [RenewalDate] column is text to smartsheet, and to convert this to a date we'll have to split this so the DATE function can work.
DATE like this: DATE(YEAR, MONTH, DAY)
- YEAR is easy, as we'll go with a YEAR(TODAY()) argument to fill it.
- MONTH is your left part of the [RenewalDate] column (assuming you're US here, otherwise, you'll have to use the other formula below. Since I'm not sure you'll type 03 or just 3 for month, we'll go with a FIND function to determine how many characters are on the LEFT. Which gives us:
- LEFT([RenewalDate]@row, FIND("/", [RenewalDate]@row)-1).
- DAY is the right part of the [RenewalDate] column, here again we go with a FIND function for the "/". But since, FIND starts from the LEFt, we'll need the LEN function as well to return the characters from the right.
- RIGHT([RenewalDate]@row, LEN([RenewalDate]@row)-FIND("/", [RenewalDate]@row))
OK, we're nearly done here as the LEFt and RIGHT returns text/string, so we need the VALUE function to convert this to numbers, so DATE can use them.
=DATE(YEAR(TODAY()), VALUE(LEFT([RenewalDate]@row, FIND("/", [RenewalDate]@row)-1)), VALUE(RIGHT([RenewalDate]@row, LEN([RenewalDate]@row)-FIND("/", [RenewalDate]@row))))
Now you do have a date that you can compare to TODAY()
=IF(DATE(YEAR(TODAY()), VALUE(LEFT([RenewalDate]@row, FIND("/", [RenewalDate]@row)-1)), VALUE(RIGHT([RenewalDate]@row, LEN([RenewalDate]@row)-FIND("/", [RenewalDate]@row))))>TODAY(),1,0)
If you're using Euro type Date display in [RenewalDate], just switch the MONTH & DAY formulas :)
Hope it helped!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!