Date format issue in a worksheet

[sharshra]

I´m using a spreadsheet where date column is formatted as d-mmm-yy. It was working correctly all these days. Suddenly, it started showing the serial number instead of date. No format change was done. I even tried to insert a new column, format that in d-mmm-yy & enter date, but it still shows the number. In the entire worksheet it shows the serial number only even the format is done for d-mmm-yy. I´m not facing this problem in other worksheets.

It is strange issue. The entire worksheet is not accepting any date format & displays the serial number. It appears as if the worksheet is locked for date format!!! Did anyone come across such issue? Any suggestions from experts please?

 

[sharshra]

Sample attached. All cells were formatted in d-mmm-yy & was working correctly. I don´t know what has changed today & it started displaying serial numbers.

trial.xlsx
	C
2	Date
4	45491
5	45491
6	45491
7	45498
A

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True

 

[Joe4]

What happens if you enter this formula in any blank cell?

=ISNUMBER(C2)

If it returns FALSE (which I expect it will), it means that you have Text entries, and not valid Date/Number entries.
Formatting only applies to entries enter as Dates/Numbers. It does not apply to any Text entries (even Numbers/Dates entered as Text).

The good news is there is any easy way to quickly convert them from Text entries to Date/Numeric ones.
Just select the whole column, go to the Data menu and select "Text to Columns" and then click Finish.
This will convert them all, and then your formatting will be applied to those and you will see it as the dates you expect.

 

[sharshra]

@Joe4, Thanks for your suggestion. But, something strange is happening. When I enter the formula, I see the whole formula instead of result. Calculation option in formulas menu is set to automatic.

Also tried text to columns but it is not changing to date

 

[Joe4]

That happens when that cell is also formatted to "Text".
I think that is the issue you are having, all your columns are set to "Text", so all entries are being entered as Text!
Change the format of that cell/column to "General" before entering the formula.

And you may want to re-format your sheet so that all columns where you might be entering data are not set to Text.

 

[sharshra]

Oops....I noticed that `Show formula´ was selected I de-selected it & I can see the correct results. Dates are appearing as dates