How display a date format instead of a DATAVALUE value when the formatting is not "TEXT."

Select the entire sheet by clicking triangle between column A and row 1 (or press CTRL+A 3 times) - Select General format again in Number group in Home tab. This should solve the problem. (In my experience, this gets triggered by some VBA code or add-in).

Hi Barry D! Perhaps, the Date within the cells had been formatted as General. You can correctly format the date as Date. * Select the cells(s) * Right-click on the selected cells and choose "Format Cells" from the context menu. * In the Format Cells dialog Select the "Date" category on the left side. * Choose the desired date format from the available options. You can select the format that matches your preference (e.g., "Short Date," "Long Date," or a custom format). * Click "OK" to apply the formatting to the selected cell Kindly let me know, if you require additional assistance, I will be glad to help further. Best Regards, Shakiru

Hi Shakiru!

Thanks for your reply, but your procedure provides no change within my spreadsheet.

I've tried several different procedures, but without success. I'm beginning to believe that my Excel workbook is corrupted as I can think of no other explanation. I may have to start another Excel spreadsheet from scratch and copy all of the data within my corrupted spreadsheet - but not the date column - and use the 'paste special' options to keep the desired formatting for all other columns. And then retype all of the dates in manually into the correct corresponding rows, but that will be time-consuming as I curently have 587 rows to add the date to! And even then, there is no garantee that the corruption won't be transfered with any copy & paste attempt. :-(

My first possible downfall was to close the spreadsheet, which prevented me from perfoming a simple 'Undo' a few times to see if that might have corrected the problem.

As a further note, my spreadsheet is simple and does not contain any formulas or even any cells formatted as 'TEXT,' so no corruption could have spread from these types of inclusions.

Thanks again for you reply.
Barry Dillon.

Hi Vijay,

Thanks for your reply, but no change occurred with your suggestion, but I have no VBA, TEXT formatting or even any formulas. Its a simple spreadsheet.

The puzzling part is that all of the cells indicate the correct formatting option that I had orginally assigned to them, but the DATEVALUE number still displays in them regardless. In experimentation, I tried the 'percentage' option and it displayed the correct date format, but, with a percent sign following - and, of course, that is not a date fromat, but it kept Excel from assiging a DATEVALUE number in that cell. Otherwise, the DATEVALUE number shows up in (all?) other formats in those cells that I have typed a date into so far.

Appreciatively Yours,

Barry Dillon

Thank you all for your support. But I have succesfully copied all of my data to a new spreadsheet (sans the formatting) without much complication - an hour well spent!

The main lesson here is to backup, backup, backup! I have deleted my old corrupted spreadsheet, so I will now consider this thread closed!

Thanks again!

Barry Dillon

Hi Barry D! Great to know you find a solution. Best Regards, Shakiru