Compare Date in [Start Date] to a value in a text/number column

johnking21406

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

Mike Wilday

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? 

Shaine Greenwood

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.