# How to convert to a date

HI all,

I'm trying to get a text string from one column (lot code) converted into a date in another column. The first column contains numbers such as: 202405 Ideally the date column would read the first four digits as a year, and the last two as the month (all of the days can be the first). So far I have:

=DATE(LEFT([LOT CODE]@row, 4), RIGHT([LOT CODE]@row, 2), 01) but it keeps throwing an "invalid data type" error. I've used =istext() to check the data type of =LEFT([LOT CODE]@row, 4) and it confirms it is text. I assumed this would be a fairly straight forward task, but I can't seem to get it to cooperate. Maybe I'm missing something obvious

Any help is much appreciated

Tags:

• You are actually very close. The LEFT and RIGHT functions output text strings, but the DATE function requires numerical values. Wrap the LEFT and RIGHT functions in a value function to convert their outputs into numbers and see if that helps.

=DATE(VALUE(LEFT([LOT CODE]@row, 4)), VALUE(RIGHT([LOT CODE]@row, 2)), 01)

• You are actually very close. The LEFT and RIGHT functions output text strings, but the DATE function requires numerical values. Wrap the LEFT and RIGHT functions in a value function to convert their outputs into numbers and see if that helps.

=DATE(VALUE(LEFT([LOT CODE]@row, 4)), VALUE(RIGHT([LOT CODE]@row, 2)), 01)

• Thanks for the hot tip, @Paul Newcome. Last week I was trying to populate a Date-column value from a YYMMDD cell (eg, 220909). It failed, and now I know why. In the meantime I came up with a satisfactory alternate approach, but I'll keep your solution handy for future reference.

Troy

• @Cleversheet The other thing you have to watch with that is the 2 digit year. I'd say we're far enough into the 2000s that (typically) a 19 won't be a factor, but it is definitely something to watch out for especially if you are tracking things like birthdays.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!