Date format issue
 

I have the cell formatted to Date 03/14/01, however, when I type in 053106
and tab it displays as 05/24/45. Seems like there is a hidden formula but I
checked and the worksheet isn't protected and has no hidden formulas.
Suggestions please.

Date format issue
 

Hi Cindy

your cell is formated as DATE, try ti format it as a text to include 053106
or general to see 53106, note that the genderal will supress the left zero.

hope this helps,
regards from Brazil
Marcelo

"CindyLF1" escreveu:

I have the cell formatted to Date 03/14/01, however, when I type in 053106
and tab it displays as 05/24/45. Seems like there is a hidden formula but I
checked and the worksheet isn't protected and has no hidden formulas.
Suggestions please.

Date format issue
 

06Hey Cindy...

The way Excel calulates dates is based off of a numbering sequence assigned
to each specific date. Here is an extract from the Excel Help tool... if
you look at todays date 06/12/06, Excel stores this date as 38880.00 so if
all you do is enter the string 053106, then Excel thinks that you are
entering a number string equivalent to a date... that date assigned to the
numberic string 53106 happens to be 05/24/2045... read the passgae below to
better understand dates in Excel...

How Microsoft Excel stores dates and times
Microsoft Excel stores dates as sequential numbers known as serial values.
Excel stores times as decimal fractions because time is considered a portion
of a day. Dates and times are values and, therefore, can be added,
subtracted, and included in other calculations. For example, to determine the
difference between two dates, you can subtract one date from the other. You
can view a date as a serial value and a time as a decimal fraction by
changing the format of the cell that contains the date or time to General
format.

Excel supports two date systems: the 1900 and 1904 date systems. The default
date system for Microsoft Excel for Windows is 1900. To change to the 1904
date system, click Options on the Tools menu, click the Calculation tab, and
then select the 1904 date system check box.

The following table shows the first date and the last date for each date
system and the serial value associated with each date.

--
Thanks for your help -
Joe Mac


"CindyLF1" wrote:

I have the cell formatted to Date 03/14/01, however, when I type in 053106
and tab it displays as 05/24/45. Seems like there is a hidden formula but I
checked and the worksheet isn't protected and has no hidden formulas.
Suggestions please.

Date format issue
 

Cindy

You cannot enter dates in the manner which you are attempting without using VBA
coding.

You must enter dates using some sort of seperator like / or -

For VBA see Chip Pearson's site.

http://www.cpearson.com/excel/DateTimeEntry.htm

Also see Chip's site for an explanation of how Excel computes dates.

http://www.cpearson.com/excel/datetime.htm#AddingDates


Gord Dibben MS Excel MVP

On Mon, 12 Jun 2006 08:17:02 -0700, CindyLF1
wrote:

I have the cell formatted to Date 03/14/01, however, when I type in 053106
and tab it displays as 05/24/45. Seems like there is a hidden formula but I
checked and the worksheet isn't protected and has no hidden formulas.
Suggestions please.