?
Solved

Excel date formats not working.

Posted on 2016-08-29
9
Medium Priority
?
61 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 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41774909
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
ID: 41774930
Thanks for the try.
I tried both formulas, neither one is working for me.

date format
0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41774955
CHIP PEARSON has an excellent Function for this here
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:chris pike
ID: 41774984
Roy,
Sorry limited VBA skills here.  
Does the code go into "ThisWorkbook"  or in "new module"
And how do i run it?
Thanks
0
 
LVL 33

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 2000 total points
ID: 41775016
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 54

Expert Comment

by:Rgonzo1971
ID: 41775069
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
ID: 41775074
Thank you very much.
Works great.
0
 

Author Comment

by:chris pike
ID: 41775075
Sorry Rgonzo I just awarded Neeraj before I saw your post.
0
 
LVL 33

Expert Comment

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

850 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