Formatting dates in Excel

Hi I have thousands of numbers to format into dates.
The problem is, for some reason when I choose the column which the
dates are in, i'll get a completely wrong date. I'll go to Format -
Cells - Date - 3/14/1998....say my number is 10504....which
basically means 01/05/04....it'll come out as 10/3/1928 .... how do I
change this??


---
Message posted from http://www.ExcelForum.com/

Hi bernrunner15,
There is a quick trick for doing this.

1) Select the column of dates.
2) Go to DataText_to_Columns on the pulldown menu.
3) leave as delimited and hit next
4) leave as tab and hit next
5) Change column date format to Date, select MDY, and hit finish

Good Luck,
Mark Graesser

Boston MA

----- bernrunner15 wrote: -----

Hi I have thousands of numbers to format into dates.
The problem is, for some reason when I choose the column which the
dates are in, i'll get a completely wrong date. I'll go to Format -
Cells - Date - 3/14/1998....say my number is 10504....which
basically means 01/05/04....it'll come out as 10/3/1928 .... how do I
change this??


---
Message posted from http://www.ExcelForum.com/

bernrunner15 wrote:

Hi I have thousands of numbers to format into dates.
The problem is, for some reason when I choose the column which the
dates are in, i'll get a completely wrong date. I'll go to Format -
Cells - Date - 3/14/1998....say my number is 10504....which
basically means 01/05/04....it'll come out as 10/3/1928 .... how do I
change this??


---
Message posted from http://www.ExcelForum.com/

from help amd support:

About dates and date systems
Show All
Hide All
Microsoft Excel stores dates as sequential numbers
which are called serial values. By default, January 1,
1900 is serial number 1, and January 1, 2008 is serial
number 39448 because it is 39,448 days after January 1,
1900. Excel stores times as decimal fractions because
time is considered a portion of a day.

Because dates and times are values, they can be added,
subtracted, and included in other calculations. 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.

Because the rules that govern the way that any
calculation program interprets dates are complex, you
should be as specific as possible about dates whenever
you enter them. This will produce the highest level of
accuracy in your date calculations.

The 1900 and 1904 date systems

Excel supports two date systems: the 1900 and 1904 date
systems. The default date system for Microsoft Excel
for Windows is 1900. The default date system for
Microsoft Excel for the Macintosh is 1904. You can
change the date system. On the Tools menu, click
Options, click the Calculation tab, and then select or
clear the 1904 date system check box.

The date system is changed automatically when you open
a document from another platform. For example, if you
are working in Excel for Windows and you open a
document created in Excel for the Macintosh, the 1904
date system check box is selected automatically.
The following table shows the first date and the last
date for each date system and the serial value
associated with each date.

Date system First date Last date
1900 January 1, 1900
(serial value 1) December 31, 9999
(serial value 2958465)
1904 January 2, 1904
(serial value 1) December 31, 9999
(serial value 2957003)

How Excel interprets two-digit years

To ensure that year values are interpreted as you
intended, type year values as four digits (2001, rather
than 01). By entering four digits for the years, Excel
won't interpret the century for you.

For Microsoft Windows 2000 or later

If you are using Microsoft Windows 2000 or later, the
Regional Options in Windows Control Panel controls how
Excel interprets two-digit years.

For dates entered as text values

When you enter a date as a text value, Excel interprets
the year as follows:

00 through 29 Excel interprets the two-digit year
values 00 through 29 as the years 2000 through 2029.
For example, if you type the date 5/28/19, Excel
assumes the date is May 28, 2019.
30 through 99 Excel interprets the two-digit year
values 30 through 99 as the years 1930 through 1999.
For example, if you type the date 5/28/98, Excel
assumes the date is May 28, 1998.

gls858

Curiously, Excel does not store dates internally, it just stores a number.
Jan-01-1900 is stored as 1, and so on. So if you put 5/11/2004 into a cell,
Excel stores it as 38118. That means that 38119 is tomorrow, 5/12, etc.
(Yes, Excel is not very good when it comes to dates before 1900 - their
stored as a piece of text).

So if you want to convert your "dates" into real dates, then you need a new
column with something like the following:
=DATE("20"&RIGHT(A24,2),MID(A24,1,LEN(A24)-4),MID(A24,LEN(A24)-3,2))

If you have dates in both centuries, you'll need:

=DATE(IF(RIGHT(A24,2)"49","20"&RIGHT(A24,2),"19"& RIGHT(A24,2)),MID(A24,1,LE
N(A24)-4),MID(A24,LEN(A24)-3,2))

"bernrunner15 " wrote in
message ...
Hi I have thousands of numbers to format into dates.
The problem is, for some reason when I choose the column which the
dates are in, i'll get a completely wrong date. I'll go to Format -
Cells - Date - 3/14/1998....say my number is 10504....which
basically means 01/05/04....it'll come out as 10/3/1928 .... how do I
change this??


---
Message posted from http://www.ExcelForum.com/

bernrunner15,

From your example, I'm afraid you have a problem. Say the number is 11104, there
is no way to tell if it means Jan 11th or Nov 1st. On the other hand, if the
number (actually text) is 110104 or 011104, then it's easy to fix.

Some good reading about dates:
http://www.cpearson.com/excel/datetime.htm#SerialDates

Regards
Anders Silven

"bernrunner15 " skrev i
meddelandet ...
Hi I have thousands of numbers to format into dates.
The problem is, for some reason when I choose the column which the
dates are in, i'll get a completely wrong date. I'll go to Format -
Cells - Date - 3/14/1998....say my number is 10504....which
basically means 01/05/04....it'll come out as 10/3/1928 .... how do I
change this??


---
Message posted from http://www.ExcelForum.com/