Convert text 202308 to date

[jlr123]

Crazy but I can't figure out how to convert 202308 from text to a date. Thanks for your assistance.

 

[Joe4]

What date is that supposed to represent, exactly?
It could be interpretted different ways.

 

[jlr123]

Year/Month 2023/Aug or 2023/08

 

[Joe4]

That is not a valid date in Excel.
A valid date needs a year, month, and day portion.

Should we just assume 1 for the day?
Or do you not really need a date, and just want a text value like "2023/08"?

If so, you can just use a formula like:

=LEFT(A1,4) & "/" & RIGHT(A1,2)

for a value in cell A1.

 

[jlr123]

I think the best would be date 2023/08/31. I have a column of dates that should return the last day of the month. thanks!

 

[Joe4]

For a date in cell A1, use this formula:

=EDATE(DATEVALUE(LEFT(A1,4) & "/" & RIGHT(A1,2)&"/01"),1)-1

and then apply your desired date format of "yyyy/mm/dd" on the cell.