Link to home
Start Free TrialLog in
Avatar of chris pike
chris pikeFlag 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.
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image
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")
Avatar of chris pike
chris pike
Flag of Canada image

ASKER

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

User generated image
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image
CHIP PEARSON has an excellent Function for this here
Avatar of chris pike
chris pike
Flag of Canada image

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
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rgonzo1971
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
Avatar of chris pike
chris pike
Flag of Canada image

ASKER

Thank you very much.
Works great.
Avatar of chris pike
chris pike
Flag of Canada image

ASKER

Sorry Rgonzo I just awarded Neeraj before I saw your post.
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image
You're welcome Chris! Glad to help.