chris pike

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.

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.

CHIP PEARSON has an excellent Function for this here

ASKER

Roy,

Sorry limited VBA skills here.

Does the code go into "ThisWorkbook" or in "new module"

And how do i run it?

Thanks

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Hi,

pls try

pls try

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

Regards
ASKER

Thank you very much.

Works great.

Works great.

ASKER

Sorry Rgonzo I just awarded Neeraj before I saw your post.

You're welcome Chris! Glad to help.

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 withdd-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")