Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Compare Date in [Start Date] to a value in a text/number column
I need to compare the date value that is in the Start Date or End Date to a date value that is in a text/number column.
When I compare the two, I get "#Invalid Operation." If I change both columns to Date columns, it works, however that does not work well with my spreadsheet.
Are there any tricks to doing this?
Thanks
Comments
-
You could convert the text field to a valid date using a variety of formulas nested in each other. https://help.smartsheet.com/function/date provides some information. How is your text date being presented? 11/11/2011? or November 11, 2011, some other way? Also, what is the desired output from comparing the two dates?
-
Hello,
To maintain data consistency, I'd recommend considering using two date columns. The text value made to look like a date may have inconsistent formatting which will throw any sort of data validation formula you have off.
With that being said, I did develop a formula that can check to see if a text value made to look like a date is the same as a date value:
=IF(DATE(2000 + VALUE(RIGHT([Text Date]7, 2)), VALUE(LEFT([Text Date]7, (FIND("/", [Text Date]7) - 1))), VALUE(MID([Text Date]7, LEN(LEFT([Text Date]7, FIND("/", [Text Date]7))) + 1, LEN(LEFT([Text Date]7, (FIND("/", [Text Date]7) - 1)))))) = Date7, "They're the Same!", "They're not the same!")
A few things to keep in mind with this:
- I made this formula to go in a third Text/Number column that will return one string if the values are the same and another if they're not.
- You'll need to replace the cell references in the formula above with cells in your sheet, and change the value_if_true and value_if_false with something that identifies with you more in your process.
- The above formula also may not work if your text values come in with any format besides mm/dd/yy. I believe the formula will work if someone enters 1/1/17 instead of 01/01/17 (January 1st, 2017), but it won't work if someone enters 1-1-17 or some other format style. You'll need to modify the formula if you need a dd/mm/yy format for your date.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K 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