change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Question

I have been searching it for an hour but unfortunately nothing found that actually work. I have an excel sheet with a column having date in it. Current date format is dd.mm.yyyy but I want to change date format to yyyy/mm/dd for entire cell. Any help will be appreciated.

Answer 1

OPTION 1)

Assuming that you source date that is in the number format dd.mm.yyyy stored as an excel date serial and only formatted to display as dd.mm.yyyy then the best fix is to select the cells you want to modify. Go to your home tab, and select the number format and change it to General. See Green circles in image below. IF the format is already set to general, or when you switch it to general your numbers do not change, then it is most likely that your date in dd.mm.yyyy format is actually text. and will needed to be converted as per OPTION 2 below. However, if the number does change when you set it to general, select the arrow in the bottom right corner of the number area (see red circle).

After clicking the arrow in the red circle you should see a screen similar to the one below:

Select Custom from the category list on the left, and then in the Type bar enter the format you want which is yyyy/mm/dd.

OPTION 2

=date(Right(A1,4),mid(A1,4,2),left(A1,2))

This assumes your original date is a string stored in A1, and converts the string to a date serial in the form excel stores dates in.¹ You can copy this formula down beside you dates. You can then apply cell formatting for the date as described above, or use the build short or long date if that style matches your needs.

¹_{Excel counts the number of days since January 0 1900 for the windows version of excel. I believe mac is 1904 or 1905.}