CODEWITHSUNDEEP
excel
sowmya ac
sowmya ac

Reputation: 43

How to change date format in Excel

I am unable to convert the date formats in column2 and column3 as shown below.

Any tips how can I change it?

I have a column 01.01.2018. This needs to be changed to 1/1/2018 and other column 01/Jan/2018

01.01.2018  1/1/2018    01/Jan/2018
01.01.2018  1/1/2018    01/Jan/2018
01.12.2017  12/1/2017   01/Dec/2017

Upvotes: 1

Views: 1828

Answers (1)

Dileep Kumar Patchigolla
Dileep Kumar Patchigolla

Reputation: 977

Your Col A has date in a format Excel doesn't recognize by default. First convert that into a proper date in Excel. You can do that by following formula:

=DATE(RIGHT(A1,4),RIGHT(LEFT(A1,5),2),LEFT(A1,2))

Where A1 refers to 01.01.2018 in your example. Now you have converted it to a date, you can change the format to following custom formats:

For format 1/1/2018:

m/d/yyyy

For Format 01/Jan/2018

[$-409]dd-mmm-yyyy;@

PS: You can refer to following link for setting custom formats in Excel:

https://www.ablebits.com/office-addins-blog/2016/07/07/custom-excel-number-format/

Upvotes: 2

Related Questions