How do I prevent Excel from automatically editing my cells??

[brianCK]

Hello!

I'm having an issue with Excel that has been frustrating me for some time now.

I am working with a data table that info regarding subjects. One of the columns is for Date of Birth (DoB). Unfortunately for me, many of the dates were either omitted or incorrectly entered (I don't think there are many 4th graders born in 1900 or 2009). Right now, the column with the DoBs appear as YYYY-MM-DD in the cells and in the formula bar, which is what i want. After changing the Format Cell options, I tried to make the corrections. I edited an incorrect one and it looks ok in its cell, but the formula bar automatically changed it to M/D/YYYY... for example:

1900-01-01 was changed to 1999-04-09... the cell looked fine but in the formula bar it reads 4/9/1999.

Normally i wouldn't mind, but I need to sort the data table by Grade - DoB - School Name... in that order. When i sort the amended table, the recently corrected cells are excluded.

DOES ANYONE HAVE ANY SUGGESTIONS?? It would be greatly, greatly appreciated... thanks!

 

[Damon Ostrander]

Hi Brian,

Excel will not normally display a date as YYYY-M-D because this is not one of the standard date display formats in Excel. But if you add this as a custom date format it will display this way. To do this, select all the cells where you want to display dates, then go to Format > Cells. Select the Custom category (at the bottom of the list), and enter the new custom format as "yyyy-m-d" in the type box, and click OK. Then enter the date in the cell or formula bar in one of the standard Excel date formats (such as m/d/y) and let the cell formatting do the rest.

That being said, I don't believe this will change the sort order. When sorting Excel dates it really doesn't matter how they are displayed--it is only the underlying date value that matters. So a sort in ascending order should place dates in past -> future order regardless of whether the date displays as YYYY-M-D or M/D/YYYY or whatever. However, the errors in date entry will always matter.

Keep Excelling.

Damon