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
Best Answer

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

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 Datecolumn 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
Categories
Check out the Formula Handbook template!