Please Help! Dates gone and list of number in it's place

A

ashmoreB

Hi,
i hope someone can help me.
I have a spread sheet which is used as a client list. In column C, we
usually have the date listed as MMM/YY (May-08). When i opened it this
morning all the dates were gone and in their place was a short list of
numbers (eg 40026). Up the top, next to the function symbol it shows the
correct date, but i have to click on the numbers to see the date.

How can i change it back to how it was previously. I'm not sure how this
happened.

thanks, B
 
S

sb1920alk

Sounds like the format has been changed. Highlight the affected area,
right-click, and choose Format. Now select one of the Data formats. Under
Custom there are many options to suit your preference.
 
A

ashmoreB

Hi, thanks, but this doesn't fix the problem. i have been trying this. the
numbers still remain.
B
 
S

sb1920alk

I'm sorry, then I don't know. When I enter 40026 in a cell and then change
the format, I get August 2, 2013 (or whatever date format I desire). Is
something (like a macro) preventing you from changing the format, or changing
it right back?

These numbers are in the cells themselves, not in a drop-down menu, right?

I can't think of anything else tonight. I'll continue to monitor this
thread. Please let me know if you find a solution.

Perhaps try entering a date in a new workbook with the format you want and
copying that cell. Then Paste Special:Formats onto the cells you're trying to
change.
 
A

ashmoreB

Thanks for your advice. Yes, the numbers are in the cells themselves.
i'm stumped!
thanks anyway.
B
 
W

ward376

40026 is the long integer that represents August 1st 2009 in Excel.
Dates are just formatted numbers. Try clearing all formats in the
range that contains your dates, then formatting as dates. Make sure
none are entered as text.

Cliff Edwards
 
D

David Biddulph

If you're getting August 2, 2013 , then it sounds like you're using the 1904
date system. Those using the default 1900 date system will be getting
August 1, 2009. You need to be careful if data is trasferred to & fro
between the 2 systems.
 
Top