Avatar of chris pike
chris pike
Flag for Canada asked on

Excel date formats not working.

Hi there experts,
I have a 6 digit date format that I want to switch to dd-mmm-yyyy

My date format looks like 080216   (Aug 08 2016)...... I need to look like,  08-Aug-2016

I have tried everything I know. I  need help.
Thanks so much.
Microsoft Excel

Avatar of undefined
Last Comment
Subodh Tiwari (Neeraj)

8/22/2022 - Mon
Subodh Tiwari (Neeraj)

There are two ways to achieve this..
Assuming your date string is in A2, then

1) try the formula =DATE("20"&RIGHT(A2,2),LEFT(A2,2),MID(A2,3,2)) and custom format the formula cells with dd-mmm-yyyy. Advantage of this method is excel will treat your dates as real dates.

2) Or try the formula =TEXT(DATE("20"&RIGHT(A2,2),LEFT(A2,2),MID(A2,3,2)),"dd-mmm-yyyy")
chris pike

ASKER
Thanks for the try.
I tried both formulas, neither one is working for me.

date format
Roy Cox

CHIP PEARSON has an excellent Function for this here
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
chris pike

ASKER
Roy,
Sorry limited VBA skills here.  
Does the code go into "ThisWorkbook"  or in "new module"
And how do i run it?
Thanks
ASKER CERTIFIED SOLUTION
Subodh Tiwari (Neeraj)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Rgonzo1971

Hi,

pls try

=TEXT(DATE("20"&RIGHT(A2,2),LEFT(A2,LEN(A2)-4),MID(TEXT(A2,"000000"),3,2))),"dd-mmm-yyyy")

Open in new window

Regards
chris pike

ASKER
Thank you very much.
Works great.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
chris pike

ASKER
Sorry Rgonzo I just awarded Neeraj before I saw your post.
Subodh Tiwari (Neeraj)

You're welcome Chris! Glad to help.