Dates display as "38854"

[Guest]

Hello,
Why are all my dates in one worksheet displaying as "38854" and similar
numbers? I'm not sure what I did, but I was working on a VB macro and that's
what happened. Also, all my percentages are displaying as "0.15" instead of
"15%".
Thanks.

 

[Guest]

Unfortunately that's not it. All the dates in all locations on the sheet
changed at once, and I made no format changes. They are all currently set
for mm/dd/yyyy, but they all display in serial number anyway. And then
there's the decimal display issue. I've never seen anything like this happen
all at once. I even tried disabling the VB module that I was working when
the problem started, but that didn't change anything.

 

[Dave Peterson]

Do you see the nice date in the formula bar?

If yes, try:
tools|options|view tab|uncheck formulas

Ctrl-` (backquote, the key to the left of the 1/! on my USA keyboard) is the
shortcut toggle for this setting.

 

[Guest]

Well, here's the code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 3 Then Exit Sub
If Intersect(Target, Columns("C:R")) Is Nothing Then
Exit Sub
End If
n = Target.Row
Cells(n, "N").Value = Format(Now, "mm-dd-yyyy")
End Sub

The purpose of this is to enter the current date in the N column any time
any piece of information in that row is updated. So it's designed to ignore
the first two rows (header rows, leave them alone), and keep everything else
updated. I tried disabling the code by commenting everything out, but I
still have the same problem. I'm not even sure that's what cause it in the
first place. Thanks again for the help.

 

[Guest]

I forgot to mention that I copied the "1" using the method you suggested, but
I still have the same display.

 

[Guest]

Your cell formatting was changed. Excel stores all dates as serial numbers
(ie. 38854). It is the Cell Format that causes the Serial Number to appear
as (5/17/2006), or whatever. Same with percentages, Excel stores percentages
as a decimal number. Again, it is the Cell Formatting that causes the
appearance to change.

Select your cells, then go to the FORMAT Menu and select CELLS. Then, on
the Number Tab, select the appropriate format that you want to see (date,
percentage, etc..)

HTH,
Elkar

 

[Guest]

Hmm... No, that code, as is, shoud not have caused the problem you've
described. Unless you've got some other code somewhere, I'm afraid I don't
have an answer for you.

 

[Guest]

Ah, I bet that's it. I never realized that the Display Formulas option had
that effect on formatted cells. Cool, I learned something today. Thanks
Dave!
Elkar

 

[Guest]

THAT WAS IT!! I really appreciate it, now I can stop pulling my hair out.