Date not recognized in column

Adamcain62

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...

Paul Newcome

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)))

Adamcain62

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 4-digit year, 2-digit month, 2-digit 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 05-11-20). 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. ; )

Paul Newcome

Is your date format

mm-dd-yy

?

Adamcain62

Thanks Paul.

hmmm, I didn't see that as an option in the drop-down - 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?

Paul Newcome

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)))

Adamcain62

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 4-digit year, 2-digit month, 2-digit 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 05-11-20). 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. ; )

Paul Newcome

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! 👍️

Adamcain62

If you hadn't, I may not have fully grasped the concept, and just copied/pasted the formula - so I'm glad! ; )