Excel date formats not working.

chris pike
chris pike used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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

Author

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

date format
Roy CoxGroup Finance Manager

Commented:
CHIP PEARSON has an excellent Function for this here
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Learn More!
chris pike

Author

Commented:
Roy,
Sorry limited VBA skills here.  
Does the code go into "ThisWorkbook"  or in "new module"
And how do i run it?
Thanks
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Okay try this....

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

Open in new window

Rgonzo1971
Top Expert 2016

Commented:
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

Author

Commented:
Thank you very much.
Works great.
chris pike

Author

Commented:
Sorry Rgonzo I just awarded Neeraj before I saw your post.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Chris! Glad to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial