Help!

Dates show up as a 5 digit number in Excel 2007

 
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> General Discussions RSS
Next:  How do I recover Word when not responding every t..  
Author Message
Dan Shelby
External


Since: Jan 25, 2006
Posts: 2



PostPosted: Mon Nov 23, 2009 7:18 pm    Post subject: Dates show up as a 5 digit number in Excel 2007
Archived from groups: microsoft>public>excel (more info?)

I have this spreadsheet in which column A contains dates entered in the
format 11/16 which, heretofore, has resulted in a cell readout of 16 Nov, or
perhaps 16-Nov.

Now, all of a sudden, the cells in column A have 5 digit numbers. Oddly
enough, the value in the formula bar still shows a valid date readout.
Also, when you place your cursor over a cell in column A, it displays a
valid date. Example, cell A1 displays a value of 40127 while the formula
bar displays 11/10/2009.

I have tried highlighting the column and changing the format without
success.

I was unable to figure out how to get Excel to display the correct values in
Column A so I changed the format of column A to Text and entered mm/dd
values. This is not what I would expect to have to do with a top-notch
spreadsheet.

Any ideas would be appreciated. Thanks in advance.
Back to top
Dave Peterson
External


Since: Jul 08, 2005
Posts: 16264



PostPosted: Mon Nov 23, 2009 7:32 pm    Post subject: Re: Dates show up as a 5 digit number in Excel 2007 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

It sounds like you're looking at formulas.

In xl2003 menus:
tools|options|view tab|uncheck formulas

I don't recall where under the Office button|excel options this is kept, but...

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

Dan Shelby wrote:
>
> I have this spreadsheet in which column A contains dates entered in the
> format 11/16 which, heretofore, has resulted in a cell readout of 16 Nov, or
> perhaps 16-Nov.
>
> Now, all of a sudden, the cells in column A have 5 digit numbers. Oddly
> enough, the value in the formula bar still shows a valid date readout.
> Also, when you place your cursor over a cell in column A, it displays a
> valid date. Example, cell A1 displays a value of 40127 while the formula
> bar displays 11/10/2009.
>
> I have tried highlighting the column and changing the format without
> success.
>
> I was unable to figure out how to get Excel to display the correct values in
> Column A so I changed the format of column A to Text and entered mm/dd
> values. This is not what I would expect to have to do with a top-notch
> spreadsheet.
>
> Any ideas would be appreciated. Thanks in advance.

--

Dave Peterson
Back to top
Rick Rothstein
External


Since: Jan 15, 2009
Posts: 127



PostPosted: Tue Nov 24, 2009 5:02 pm    Post subject: Re: Dates show up as a 5 digit number in Excel 2007 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

> It sounds like you're looking at formulas.
>
> In xl2003 menus:
> tools|options|view tab|uncheck formulas
>
> I don't recall where under the Office button|excel options this is kept...

In XL2007.... click the Office Button, click the Excel Options button,
select Advanced from the left-hand listing, scroll down to the "Display
options for this workbook" section and uncheck the CheckBox labeled "Show
formulas in cells instead of their calculated results".

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message

> It sounds like you're looking at formulas.
>
> In xl2003 menus:
> tools|options|view tab|uncheck formulas
>
> I don't recall where under the Office button|excel options this is kept,
> but...
>
> ctrl-` (control-backquote, the key to the left of the 1/! on my USA
> keyboard) is
> the shortcut key for toggling this setting.
>
> Dan Shelby wrote:
>>
>> I have this spreadsheet in which column A contains dates entered in the
>> format 11/16 which, heretofore, has resulted in a cell readout of 16 Nov,
>> or
>> perhaps 16-Nov.
>>
>> Now, all of a sudden, the cells in column A have 5 digit numbers. Oddly
>> enough, the value in the formula bar still shows a valid date readout.
>> Also, when you place your cursor over a cell in column A, it displays a
>> valid date. Example, cell A1 displays a value of 40127 while the formula
>> bar displays 11/10/2009.
>>
>> I have tried highlighting the column and changing the format without
>> success.
>>
>> I was unable to figure out how to get Excel to display the correct values
>> in
>> Column A so I changed the format of column A to Text and entered mm/dd
>> values. This is not what I would expect to have to do with a top-notch
>> spreadsheet.
>>
>> Any ideas would be appreciated. Thanks in advance.
>
> --
>
> Dave Peterson
Back to top
Dave Peterson
External


Since: Jul 08, 2005
Posts: 16264



PostPosted: Tue Nov 24, 2009 5:17 pm    Post subject: Re: Dates show up as a 5 digit number in Excel 2007 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Just where you'd expect it <vbg>...

Thanks Rick.

Rick Rothstein wrote:
>
> > It sounds like you're looking at formulas.
> >
> > In xl2003 menus:
> > tools|options|view tab|uncheck formulas
> >
> > I don't recall where under the Office button|excel options this is kept...
>
> In XL2007.... click the Office Button, click the Excel Options button,
> select Advanced from the left-hand listing, scroll down to the "Display
> options for this workbook" section and uncheck the CheckBox labeled "Show
> formulas in cells instead of their calculated results".
>
> --
> Rick (MVP - Excel)
>
> "Dave Peterson" wrote in message
>
> > It sounds like you're looking at formulas.
> >
> > In xl2003 menus:
> > tools|options|view tab|uncheck formulas
> >
> > I don't recall where under the Office button|excel options this is kept,
> > but...
> >
> > ctrl-` (control-backquote, the key to the left of the 1/! on my USA
> > keyboard) is
> > the shortcut key for toggling this setting.
> >
> > Dan Shelby wrote:
> >>
> >> I have this spreadsheet in which column A contains dates entered in the
> >> format 11/16 which, heretofore, has resulted in a cell readout of 16 Nov,
> >> or
> >> perhaps 16-Nov.
> >>
> >> Now, all of a sudden, the cells in column A have 5 digit numbers. Oddly
> >> enough, the value in the formula bar still shows a valid date readout.
> >> Also, when you place your cursor over a cell in column A, it displays a
> >> valid date. Example, cell A1 displays a value of 40127 while the formula
> >> bar displays 11/10/2009.
> >>
> >> I have tried highlighting the column and changing the format without
> >> success.
> >>
> >> I was unable to figure out how to get Excel to display the correct values
> >> in
> >> Column A so I changed the format of column A to Text and entered mm/dd
> >> values. This is not what I would expect to have to do with a top-notch
> >> spreadsheet.
> >>
> >> Any ideas would be appreciated. Thanks in advance.
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> General Discussions All times are: Eastern Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum