Software

Office challenge: Why is Excel displaying a date instead of a numeric value?

This week, test your Excel skills by solving this common but peculiar and annoying formatting snafu.

You don't always get what you expect when using Excel. Most of the time, users just shrug and work around the unexpected, if they know how. Occasionally, they call you because they just don't have the experience to avoid the problem.

Suppose a user calls you after entering the numeric value 90 because Excel displayed the date 3/30/1900 instead of 90. Even the formula bar is displaying a date-Excel ate her value! She swears that she didn't format the cell to display a date, and you believe her.

You can help her correctly display the numeric value quickly enough; even she knows how to do that. But can you explain why Excel displayed a date instead of a value? By helping her expose why this happened, you'll help her learn how to avoid this common, but unexpected formatting surprise.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

16 comments
ppg
ppg

If she accidently copied a date to the cell even if she deleted the date and then entered the correct number, Excel still treats it as having a date format. You would have to clear formats as swell as contents before it goes back to acting as a number

onasportster
onasportster

Just as many Excel users don't realize that formulas universally (and internally) compute to 15-digits of significance; dates are seen as a whole number. Excel tracks every date since January 1st, 1900 as a number. It does this so when calculating the amount of time between two dates, it knows how to return a whole number. this is a nice feature for Payroll and other Accounting professions!

sbsinc
sbsinc

You have created headers for your column and Excel presumes that the one on the left, Date, is what you want for the next column, Quota. Once you change the format of cell B2 to General then the rest of the rows in column B will be formatted as General.

cme
cme

There could be a number of reasons excel is formatting the cell with a date, but the quickest solution is to format the column at "Text" as this will display exactly what is typed in the cell

venkat1926
venkat1926

will it not be better select all cells(control+A) and format cells as general. then excel will guess the format of entry.

Rudi-S
Rudi-S

Agreed. My first thought too was that a column was inserted to accommodate the quota, and Excel takes the formatting from the left. Another time where Excel changes to date format automatically is when one calculates with dates; like: =[Mydate]+15. The resulting value will be a date, even though the cell was formatted to general.

Shriks
Shriks

Faced this many times. I keep track of budgets data in excel Have found if I save .xls as .xlsx this happens quite often. It's just saving that causes this to happen not formatting the columns. Not only that, once saved in .xlsx If I open the file at any time I fear I might see ####### in all those number cells. There's something more of an issue here than just changing format.

hometoy
hometoy

[i]But can you explain why Excel displayed a date instead of a value? [/i] She could have made the column by inserting it, in which case it takes the property (format) of the cell to the left. Even if you are clicking the "Personnel" column and select "Insert" the new column (on the left of Personnel) will take the properties of the column to the left of the new one. You can test this by coloring one column ("A") yellow, and the second column ("B") blue. Right-click on column "B" and select Insert. It will be Yellow, just like Column A.

Jeromied
Jeromied

Under (2010) File Options, Formulas, there is a check box for dates ending in 2 digits, that should be unchecked.

jamesski
jamesski

Change to general or number..

Marshwiggle
Marshwiggle

... since both "Date" and "Quota" would be formatted as Text or General. Did you try it?

Marshwiggle
Marshwiggle

... that in translating between .xls and .xlsx formats Excel is also changing the column width? or some other formatting that causes the value to no longer fit the column? I've heard of this happening often if a workbook is originated in Office for Macs, then shared with somebody using Office for Windows, or vice versa.

Marshwiggle
Marshwiggle

... to remind users that similarly, an inserted row will take the formatting of the row above.

CrankyOldBugger
CrankyOldBugger

Inserting a column will indeed take the formatting from the immediate left column, so this could be where she got the incorrect formatting. Rudi-S's comment on using dates in formulas is also a good reason. Best solution: get a smarter user!

ptanrioger
ptanrioger

Thanks, hometoy! I didn't know that an inserted column takes on the formatting of the column to the left of it. It's always good to learn something new!

Editor's Picks