Date not recognized in column
I have data coming into Smartsheet from a Microsoft Form, and I'm not sure how to get smartsheet to recognize the data as a date. I tried to create an additional Date column, and putting in a formula to copy the date from the original column (hoping that the settings of the new date column would make the data officially a date, but it just appears to be text). Tried creating reports based on the date column and it doesn't recognize the data as a date. Seems like this should be easy and I'm just missing something obvious...
Best Answers

I meant if that was the order of your numbers in your text column. We can use a DATE function, but we first need to know which numbers are which meaning your month comes first, then your day, then your two digit year.
Try this...
=DATE(VALUE("20" + RIGHT([Date of Orientation]@row, 2)), VALUE(LEFT([Date of Orientation]@row, 2)), VALUE(MID([Date of Orientation]@row, 3, 2)))

Thank you so much Paul! (I had seen a similar formula elsewhere, but couldn't understand the logic to it to convert it.
So for others like myself without experience using the DATE or LEFT/RIGHT/MID functions, a quick explanation: First, the formula starts with a DATE function, which takes the format of 4digit year, 2digit month, 2digit day. Next, taking the VALUE of the 'text', which makes sure the formula will be converting to numbers. The "+20" is to convert the 2 digit year to a 4 digit year (2020), so adding the 2 additional numbers '20' to the front of the 2 numbers (another '20') that appear at the end (or furthermost RIGHT in the text) creates '2020'. (If the year was 2120, it would be "+21" instead of "+20"). Next is grabbing the Month, which appears on the furthermost LEFT of the text. So taking the first 2 characters from the LEFT of the text of 05/11/20, will give the month. Finally getting the day, which appears in the middle, starting with the 4th character in the text (text example is 05/11/20, or 051120). The MID formula requires 2 parts (unlike the LEFT or RIGHT formula), because it needs to know where to start, since it isn't the furthermost left or right in the text). So taking the text, starting with the 4th character, and grabbing the next 2 characters from the text field, turning it to a number with the VALUE function, will finally give the full DATE function needed for Smartsheet to recognize it as a valid Date.
(I just had to make one small change to the formula above in regards to the MID function  the character starts on the 4th instead of 3rd character  the 3rd is a slash or dash instead of the month number). So finally: =DATE(VALUE("20" + RIGHT([Date of Orientation]@row, 2)), VALUE(LEFT([Date of Orientation]@row, 2)), VALUE(MID([Date of Orientation]@row, 4, 2)))
Thanks again Paul! Fixed my problem, plus learned a few more functions. ; )
Answers


Thanks Paul.
hmmm, I didn't see that as an option in the dropdown  so I changed the information coming in to the default format of "04/27/18". The information copied from column 2 (a Text column), is still recognized as text, and not a date. Is there maybe a different way duplicate the row (different formula?) to make sure I'm getting something in a date format?

I meant if that was the order of your numbers in your text column. We can use a DATE function, but we first need to know which numbers are which meaning your month comes first, then your day, then your two digit year.
Try this...
=DATE(VALUE("20" + RIGHT([Date of Orientation]@row, 2)), VALUE(LEFT([Date of Orientation]@row, 2)), VALUE(MID([Date of Orientation]@row, 3, 2)))

Thank you so much Paul! (I had seen a similar formula elsewhere, but couldn't understand the logic to it to convert it.
So for others like myself without experience using the DATE or LEFT/RIGHT/MID functions, a quick explanation: First, the formula starts with a DATE function, which takes the format of 4digit year, 2digit month, 2digit day. Next, taking the VALUE of the 'text', which makes sure the formula will be converting to numbers. The "+20" is to convert the 2 digit year to a 4 digit year (2020), so adding the 2 additional numbers '20' to the front of the 2 numbers (another '20') that appear at the end (or furthermost RIGHT in the text) creates '2020'. (If the year was 2120, it would be "+21" instead of "+20"). Next is grabbing the Month, which appears on the furthermost LEFT of the text. So taking the first 2 characters from the LEFT of the text of 05/11/20, will give the month. Finally getting the day, which appears in the middle, starting with the 4th character in the text (text example is 05/11/20, or 051120). The MID formula requires 2 parts (unlike the LEFT or RIGHT formula), because it needs to know where to start, since it isn't the furthermost left or right in the text). So taking the text, starting with the 4th character, and grabbing the next 2 characters from the text field, turning it to a number with the VALUE function, will finally give the full DATE function needed for Smartsheet to recognize it as a valid Date.
(I just had to make one small change to the formula above in regards to the MID function  the character starts on the 4th instead of 3rd character  the 3rd is a slash or dash instead of the month number). So finally: =DATE(VALUE("20" + RIGHT([Date of Orientation]@row, 2)), VALUE(LEFT([Date of Orientation]@row, 2)), VALUE(MID([Date of Orientation]@row, 4, 2)))
Thanks again Paul! Fixed my problem, plus learned a few more functions. ; )

It looks like you fully understand exactly what is happening.
And sorry about the MID thing. I do that a lot even in my own sheets. Ugh.
Happy to help! 👍️

If you hadn't, I may not have fully grasped the concept, and just copied/pasted the formula  so I'm glad! ; )
Help Article Resources
Categories
Check out the Formula Handbook template!