How to convert to a date

ashleyp

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

Paul Newcome

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)

Paul Newcome

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)

Cleversheet

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

Paul Newcome

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