Solved date appears as numbers

October 17, 2012 at 12:32:43
Specs: Windows 7
when we enter 10/17/2012 it shows as 39737.
The date use to work, but now it does not. Did we push a button somewhere?

See More: date appears as numbers

Report •


✔ Best Answer
October 18, 2012 at 18:12:45
A few things to try:

I - Make sure that the sheet is not set to display formulas.

See here:

http://support.microsoft.com/kb/241072

II - If that doesn't help, try this:

1 - Copy a blank cell
2 - Select the date cells
3 - Edit > Paste Special, Add, Values.
4 - Format as date

III - If that doesn't Help, try this:

1 - Select the date cells.
2 - Data...Text To Columns...Delimited...Next...Next...Finish
3 - Format as date

IV - If that doesn't work, try the second suggestion again. (Suggestion 3 may force the values to be text, adding a blank cell will turn them back into numbers.

Remember to check the format to make sure it is set to Date after trying anything. The format may have changed to something else without you knowing it.

Good Luck!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
October 17, 2012 at 13:08:53
Where (in which program) did it show the date as a number?

Report •

#2
October 17, 2012 at 13:31:16
in an excel spreed sheet, in a colum marked as date

Report •

#3
October 17, 2012 at 14:56:43
I would check the date format, for the cells, perhaps settings got changed by mistake.

:: mike


Report •

Related Solutions

#4
October 17, 2012 at 16:25:01
The cell is set to default and must be changed to date format.

Report •

#5
October 17, 2012 at 16:39:10
Excel stores dates and times as a number, with the integer portion of the number being the number of days since Excel's "beginning of time". The decimal portion is the time, based on 24 hours.

Excel actually has 2 default "beginning of time" dates. The default for Windows is 1/1/1900, therefore Day 1 is 1/1/1900, Day 39737 is 10/16/2008.

The default for Mac is 1/1/1904, therefore Day 1 is 1/1/1904, Day 39737 is 10/17/2012.

Using the 1904 date system, which you appear to be using, 39737.5 would be 12 noon on 10/17/2012, 39737.75 would be 6:00PM on 10/17/2012.

So, it seems that the format of the cells was changed from Date to Number or General. Change the formats to Date and you should get your dates back.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
October 18, 2012 at 14:50:30
I tried that and nothing happened. The date shows in the formula bar, but it shows as a number in the "date" cell.

Report •

#7
October 18, 2012 at 14:56:14
Post the formula, shown in the formula bar of that cell.

Report •

#8
October 18, 2012 at 15:19:58
there is no formula, just a date shows. I tried adding a formula, but a number still appears when I put in a date.

Report •

#9
October 18, 2012 at 15:22:44
Seems, that a formula in another cell is filling the cell in question.

So question is, did you always get the same number in that cell?


Report •

#10
October 18, 2012 at 15:30:55
No the number increases day by day. The same number appears for each day, example yesterday was 39737, today is 39738.
The entire record from 1998 until today shows a number. Someone clicked a button somewhere that changed this format.
I have to leave work now, I will follow up tomorrow. Thanks

Report •

#11
October 18, 2012 at 18:12:45
✔ Best Answer
A few things to try:

I - Make sure that the sheet is not set to display formulas.

See here:

http://support.microsoft.com/kb/241072

II - If that doesn't help, try this:

1 - Copy a blank cell
2 - Select the date cells
3 - Edit > Paste Special, Add, Values.
4 - Format as date

III - If that doesn't Help, try this:

1 - Select the date cells.
2 - Data...Text To Columns...Delimited...Next...Next...Finish
3 - Format as date

IV - If that doesn't work, try the second suggestion again. (Suggestion 3 may force the values to be text, adding a blank cell will turn them back into numbers.

Remember to check the format to make sure it is set to Date after trying anything. The format may have changed to something else without you knowing it.

Good Luck!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
October 19, 2012 at 11:05:01
Yes that fixed the problem. Thanks to all who gave their input.

Report •

#13
October 19, 2012 at 11:42:46
Which "that" fixed the problem?

Multiple suggestions were given and it might help others if you tell us which "that" fixed the problem so that the solution gets stored in the archives of this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question