Solved

Excel date formats not working.

Posted on 2016-08-29
9
42 Views
Last Modified: 2016-08-29
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.
0
Comment
Question by:chris pike
9 Comments
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
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")
0
 

Author Comment

by:chris pike
Comment Utility
Thanks for the try.
I tried both formulas, neither one is working for me.

date format
0
 
LVL 17

Expert Comment

by:Roy_Cox
Comment Utility
CHIP PEARSON has an excellent Function for this here
0
 

Author Comment

by:chris pike
Comment Utility
Roy,
Sorry limited VBA skills here.  
Does the code go into "ThisWorkbook"  or in "new module"
And how do i run it?
Thanks
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
Comment Utility
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

0
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
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
0
 

Author Closing Comment

by:chris pike
Comment Utility
Thank you very much.
Works great.
0
 

Author Comment

by:chris pike
Comment Utility
Sorry Rgonzo I just awarded Neeraj before I saw your post.
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
You're welcome Chris! Glad to help.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now