extracting month, day and year

[rochenge]

Dear Folks, I have a problem here,
I have the following worksheet with a column of date from which i wish to separately extract, day, month and year; I applied the formula =month(date) and it returned 01/01/1900; what i was looking for is a single value; for jan=1 feb=2,... and for a day obtain 1,2 ... Help identify the problem. Additionally, i have tried to use the icon for attachment for posting the excel worksheet but the icon does not work, or how do I attach the relevant worksheet in this forum?
Date month
02/01/2009
05/01/2009
06/01/2009
07/01/2009
08/01/2009
09/01/2009
12/01/2009
13/01/2009
14/01/2009
15/01/2009
16/01/2009
19/01/2009
20/01/2009
21/01/2009
22/01/2009
23/01/2009

[JohnTopley]

Try

Data in column A

in B2

=DAY(A2)

in C2

=MONTH(A2)

in D2

=YEAR(A2)

Copy down columns

[Tony Valko]

Your formula for the month is correct. You just need to format the result as General or Number.

For the day and year:

=DAY(A1)

=YEAR(A1)

Format both of these as General or Number.

[sandy666]

or:
=TEXT(A1,"d") for number of day or =TEXT(A1,"dddd") for a name of day
=TEXT(A1,"mmm") for short name of month or =TEXT(A1,"mmmm") for long name of month
=TEXT(A1,"yyyy") for full year

[AlKey]

Enter formula in B2 and copy across and down

Please Login or Register  to view this content.

v	A	B	C	D
1	Date	Day	Month	Year
2	1/2/2009	2	1	2009
3	1/5/2009	5	1	2009
4	1/6/2009	6	1	2009
5	1/7/2009	7	1	2009
6	1/8/2009	8	1	2009
7	1/9/2009	9	1	2009
8	1/12/2009	12	1	2009
9	1/13/2009	13	1	2009
10	1/14/2009	14	1	2009
11	1/15/2009	15	1	2009
12	1/16/2009	16	1	2009
13	1/19/2009	19	1	2009
14	1/20/2009	20	1	2009
15	1/21/2009	21	1	2009
16	1/22/2009	22	1	2009
17	1/23/2009	23	1	2009

[rochenge]

Thanks Valko, it worked, be blessed

[Tony Valko]

You're welcome. Thanks for the feedback!