Excel 2003 changing date entries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

A couple of users and myself are having a strange problem with excel; when
you select the cell and format for "date" and then select the way it should
be specified - for example 03/14/05 - and then enter in a date such 031405
excel returns an entry of 12/24/85. I know that if you enter in 03/14/05 it
stays that way but how come with out the back slashes it comes out with a
different date altogether?
 
Because Excel store dates as numbers. Today, Dec 5, 2005 = 38691.

You see a date because Excel is displaying 38691 as a date (presumably
because you've formatted the cell that way).

The use of backslashes during entry "tells" excel that you are entering a
date. It then converts that date value to the equivalent number value, which
it stores, and then displays that number with the format you have chosen: a
date.

When you make an entry without backslashes, Excel assumes you are entering a
number, which it stores, and then displays with your specified format.
(31405 formatted as a date displays 12/24/85)

HTH,
 
It is seeing the number as a serial number. The serial number for 12/24/85
is 31405. A work around, if you hate typing slashes is:
In cell A1 type 031405.
then Data menu/Text to columns. In the wizard chose fixed width. Put column
breaks between the 3 and 1 and the 4 and 0. Next. Make the Destination cell
B1. Finish.
This should put 3 in B1, 14 in C1, and 5 in D1. Then in E1 put the formula
=DATE(D1,B1,C1). It will return 03/14/1905. Unfortunately, it sees the 5 as
year 1905 instead of 2005, so you'll have to either change them manually or
type them in origanally as 03142005.
Obviously not worth it for one date, but if you are typing a lot or
importing from a text source, you can do this and then hide the columns A-D.
Or copy and paste special=values the final result in colum E.
 
what you are telling me makes sense but it seems like that is a flaw in the
program to both me and the users, it seems that it would be more user
friendly if that did not occur.
 
This almost got me there. I have a very long list of numbers. Some have 6
digits and some have five digits ie: 31505 vs 122505. By using the custom
number function I can change thenm all to be 6 characters, but the text to
columns work around does not see the leading zeros. Therefore, I needed to
break my spreadsheet into two. do you have a shorter workaround for that
problem?
 
Hello:

Here is an alternative that does not require two sheets and will handle both
2000+ dates and earlier ones (just in case you are using dates
from the late 1900s)

Assume you have your dates entered in column A then add the helper columns
B through E as below:

Cell A1: 31505
Cell B1: =IF(LEN(A2)=6,MID(A2,1,2),MID(A2,1,1))
Cell C1: =IF(LEN(A2)=6,MID(A2,3,2),MID(A2,2,2))
Cell D1: =RIGHT(A2,2)
Cell E1: =DATE(D2+IF(VALUE(D2)<20,2000,1900),B2,C2)


You date should now appear in E1. Just copy the rows down as far as you
need. Note the forumla in E1 assume any years less than 20 is in the 2000s
and any year greater than that is in the 1900s.

You could get rid of the formula in column E by doing a copy/paste special
values when you are done. Then you have a column of dates that can copied
or used as you like

Pieter Vandenberg


: This almost got me there. I have a very long list of numbers. Some have 6
: digits and some have five digits ie: 31505 vs 122505. By using the custom
: number function I can change thenm all to be 6 characters, but the text to
: columns work around does not see the leading zeros. Therefore, I needed to
: break my spreadsheet into two. do you have a shorter workaround for that
: problem?

: "jlucy" wrote:

:> It is seeing the number as a serial number. The serial number for 12/24/85
:> is 31405. A work around, if you hate typing slashes is:
:> In cell A1 type 031405.
:> then Data menu/Text to columns. In the wizard chose fixed width. Put column
:> breaks between the 3 and 1 and the 4 and 0. Next. Make the Destination cell
:> B1. Finish.
:> This should put 3 in B1, 14 in C1, and 5 in D1. Then in E1 put the formula
:> =DATE(D1,B1,C1). It will return 03/14/1905. Unfortunately, it sees the 5 as
:> year 1905 instead of 2005, so you'll have to either change them manually or
:> type them in origanally as 03142005.
:> Obviously not worth it for one date, but if you are typing a lot or
:> importing from a text source, you can do this and then hide the columns A-D.
:> Or copy and paste special=values the final result in colum E.
:>
:> "Christine" wrote:
:>
:> > A couple of users and myself are having a strange problem with excel; when
:> > you select the cell and format for "date" and then select the way it should
:> > be specified - for example 03/14/05 - and then enter in a date such 031405
:> > excel returns an entry of 12/24/85. I know that if you enter in 03/14/05 it
:> > stays that way but how come with out the back slashes it comes out with a
:> > different date altogether?
 
Back
Top