How to convert unusual date number to a date format

[tytuck229]

I get excel sheets with dates like the following and have to constantly convert them to another date format: 190501 and want to create a formula to automatically convert this to 05/01/2019.

[AliGW]

Welcome to the forum!

Try this:

=DATE(YEAR(2000+LEFT(A1,2)),MONTH(MID(A1,2,2),DAY(RIGHT(A1,2))

[jeffreybrown]

Or maybe...

=(MID(A1,3,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,2))+0 >> format as data how you desire

@Ali, your submission seems to be missing a parenthesis and the mid function should be 3 and not 2

[jason.b75]

Try

Please Login or Register  to view this content.

Or use text to columns, changing the date format dropdown in the last tab to YMD

[AliGW]

Thanks, Jeff.

=DATE(YEAR(2000+LEFT(A1,2)),MONTH(MID(A1,3,2)),DAY(RIGHT(A1,2))

[jason.b75]

You might want to test that formula, Ali

[shg]

Another way:

	A	B	C
2	190501	01 May 2019	B2: =--TEXT(A1, "2\000-00-00")

[AliGW]

You seem to be enjoying catching me out today, Jason!

You've corrected it in post #4, so no need for me to bother.

[jason.b75]

You seem to be enjoying catching me out today, Jason!

Not deliberately, honest guv! You just seem to be having an off day, I'll try to avoid looking at any more of your answers, just in case

I think the rep in ths thread has to go to shg for the simple and elegant solution that we all missed though.

[AliGW]

Yup - one or two gaffs, but actually quite a lot that have been spot on today for me!!!

Kudos to SHG.

[Fluff13]

Alternatively, you can just use text to columns & select YMD

[tytuck229]

This helped! Thank you so much!!