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

johnking21406
johnking21406 ✭
edited 12/09/19 in Archived 2017 Posts

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

Tags:
· Share on FacebookShare on Twitter

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    edited 08/23/17

    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? 

    · Share on FacebookShare on Twitter
  • Shaine Greenwood
    Shaine Greenwood Employee

    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.
    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories