cell won't change to correct date format

cstewart28

New Member
Joined
May 4, 2016
Messages
7
I have a column that I change to a short date format, but the contents of the cell do not change. Here are the steps I do:

1. I download some data from a web site, then I open it up in Excel 2016, it gives me some notification about opening files from the Internet. I save the files as xlsx.

2. I then reopen it and try and change the date format from 01/02/2018 to my regional setting on my computer which is yyyy-mm-dd, but it does change it.

3. I select format cell and it shows the correct format, but the cell is still displayed as 01/02/2018.

4. I have tried saving as csv then reopening it and still the same results. It shows as it if formatted properly.

Not sure how to fix this.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
custom format

date are whole numbers, formatting the cells is what controls what you see
 

cstewart28

New Member
Joined
May 4, 2016
Messages
7
I have also tried to select the column, then change the format to custom to yyyy-mm-dd, but no change in data.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,898
Office Version
  1. 365
Platform
  1. Windows
Custom formats only work on entries that are entered as numbers (dates are actually formatted numbers in Excel).
If changing the format of a cell does not change what you see, that means that your entry is NOT a valid numeric/date entry, but rather a Text entry. Formatting does not work on Text entries.

One quick way to change this is to highlight the column, use Text To Columns, and switch it to the proper date option in Step 3.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,344
Messages
5,547,377
Members
410,790
Latest member
EvdM
Top