Excel date problem

June 25, 2004 10:06 AM

RE: Excel: I have an Excel spreadsheet where one column is filled with dates — but the dates aren't showing up properly in the spreadsheet. Instead of looking like a date, they are flat numbers (02/29/2004 is rendered "36584" in the display) — even though the proper date is revealed in the FORMULA BAR as I select each cell. I've set the text type to DATE in the FORMAT dialog... I've even tried setting the text type to PLAIN TEXT. No go. Strangely, the numbers increment with the date (03/07/2004 is "36591"), so I'm guessing there's some equation going on. Suggestions on getting the dates to render properly would be appreciated.
posted by silusGROK to computers & internet (10 comments total)

I use Excel for OS X, but it should be close.

Select the column. Go to Format: Cells. Click the Number tab. Click the Date item in the list of number formats. Then select the date format you like, or click Custom and make up your own. For example "dd MMM yyyy" would result in 25 JUN 2004.
posted by adamrice at 10:16 AM on June 25, 2004

adamrice is correct. You need to format the column. The number you are seeing is the number of days starting from Jan 0, 1900 (obviously, the lowest date possible is therefore 1, for Jan 1, 1900).
Dates with times includes would be decimals, such as 1.1, with 0.1 being 1/10 of 24 hours.
posted by linux at 10:20 AM on June 25, 2004

As I mentioned in the question, I've already tried setting the formatting to DATE in the column via the FORMAT dialog... nothing changes.

For a while I thought that it might be a corrupt preferences file, but others see the date just as I do.
posted by silusGROK at 1:04 PM on June 25, 2004

I don't have a good idea as to what is wrong, but this is worth a try:

1) Insert a new column to the right of the existing date column
(select the column to the right of the date column, and choose
"Columns" from the Insert menu).
2) Select the entire new column, and format it to Date.
3) Select the existing date column and copy it to the clipboard.
4) Select the new column.
5) Edit->Paste Special. Check the box for "Values" on the Paste Special dialog.

If that works, delete the old column, save the workbook, and carry on.
posted by crunchburger at 1:40 PM on June 25, 2004

Interestingly, a non-MeFite just e-mailed me the following, which I will paste forthwith:

I think all he/she needs to do is press CTRL + ` (the key to the left of the "1" on a standard PC keyboard). This key combination toggles between showing cell values and cell formulas in Excel. I don't know if a Mac uses the same keys, but the idea should be the same.

I tried this on my Mac just now and it works.
posted by adamrice at 2:09 PM on June 25, 2004

press CTRL + `

This is so cool.
posted by rhapsodie at 2:50 PM on June 25, 2004

Silus:

did you also follow the first advice given by adam? the 36591-style display is, indeed, a valid DATE format. It's the underlying native DATE format that XL uses; explicitly setting one of the more conventional formats may help. That said, crtl+` sounds worth investigating.
posted by mwhybark at 4:37 PM on June 25, 2004

Ctrl + ' sounds like the best theory so far, and it's news to me. Thanks, adamrice's contact!

If so, my idea will be useless - the issue is the expression evaluating layer, not the cell format. Are there any numeric formula in the spreadsheet?
posted by crunchburger at 7:22 PM on June 25, 2004

Thanks everyone!

It _was_ the CTRL+`

Very cool.
posted by silusGROK at 11:02 PM on June 25, 2004

Also, if you select cell A1, hold down CTRL, and scroll the mouse wheel, you will change the resolution of the whole worksheet.
not that anyone asked
posted by bingo at 7:56 AM on June 26, 2004