Divide data into multiple cells

[treehstn]

Hi everyone,

I have a large data set that I need to divide up the dates from one column of values into three. This is an image of the current format and the format I need it in. Can anyone help me with the process to do this?

Thanks!

 

[T. Valko]

Hi everyone,

I have a large data set that I need to divide up the dates from one column of values into three. This is an image of the current format and the format I need it in. Can anyone help me with the process to do this?

Thanks!

Book1
	A	B	C	D	E
1	Date		Month	Day	Year
2	7/14/2001		7	14	2001
3	1/31/2004		1	31	2004
4	12/28/2002		12	28	2002
5	4/8/2002		4	8	2002
6	10/16/2013		10	16	2013
7	8/2/2004		8	2	2004
8	2/15/2011		2	15	2011
9	8/25/2012		8	25	2012
10	2/25/2009		2	25	2009
Sheet1

Formulas:

C2: =MONTH(A2)
D2: =DAY(A2)
E2: =YEAR(A2)

Copy down as needed.

 

[treehstn]

Formulas:

C2: =MONTH(A2)
D2: =DAY(A2)
E2: =YEAR(A2)

Copy down as needed.

I have a field that reads 4/8/1900. When I put =MONTH(A2) in the cell next to it I get this: 1/4/1900.

 

[T. Valko]

I have a field that reads 4/8/1900. When I put =MONTH(A2) in the cell next to it I get this: 1/4/1900.

Format all of the formula cells as General.

 

[treehstn]

Then I got #VALUE!

The original date column is formatted as general. I got the value error also when I changed that column to date. Didn't work either way.

 

[T. Valko]

Then I got #VALUE!

The original date column is formatted as general. I got the value error also when I changed that column to date. Didn't work either way.

Are your dates true Excel dates or are they TEXT strings that look like dates?

In Excel true dates are really just numbers formatted to look like a date.

If A1 contains a true Excel date this formula will return TRUE:

=ISNUMBER(A1)

 

[treehstn]

They must not be. It returned FALSE.

 

[T. Valko]

They must not be. It returned FALSE.

Are any of these dates before 1/1/1900? Excel doesn't recognize dates before 1/1/1900.

What version of Excel are you using?

 

[treehstn]

Yes there are a lot that are in the 1800s... maybe I'll have to change this all by hand?

 

[treehstn]

Whoops.. sorry forgot to say Excel 2007.