CODEWITHSUNDEEP
exceldateexcel-formulaformatting
gvncore
gvncore

Reputation: 43

How to convert a custom date format in excel?

I have the following date format: mmm-dd-yyyy (e.g. Sep-10-2013)

I need it to convert it to this one: dd.mm.yy (e.g. 10.09.13)

Anybody an idea how what function might solve this issue? Thanks for any advice.

Upvotes: 0

Views: 284

Answers (2)

Anabas
Anabas

Reputation: 356

You should check your cells format of date. Or use formula, (eg, A1 = Sep-10-2013) 

=MONTH(A1)&"."&DAY(A1)&"."&RIGHT(YEAR(A1),2)

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

With data in A1, in B1 enter:

=IF(ISNUMBER(A1),A1,DATEVALUE(MID(A1,5,2)&" "&LEFT(A1,3)&" "&RIGHT(A1,4)))

and then set your custom format in cell B1:

enter image description here

NOTE:

To change a date's format, we need to know if the input is a genuine Excel date or only a text string. This formula does the testing for you.

Upvotes: 3

Related Questions