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.
chris pikeAsked:
Who is Participating?
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Okay try this....


Open in new window

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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 pikeAuthor Commented:
Thanks for the try.
I tried both formulas, neither one is working for me.

date format
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Roy CoxGroup Finance ManagerCommented:
CHIP PEARSON has an excellent Function for this here
chris pikeAuthor Commented:
Sorry limited VBA skills here.  
Does the code go into "ThisWorkbook"  or in "new module"
And how do i run it?

pls try


Open in new window

chris pikeAuthor Commented:
Thank you very much.
Works great.
chris pikeAuthor Commented:
Sorry Rgonzo I just awarded Neeraj before I saw your post.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Chris! Glad to help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.