Excel - digit separation for dates

[Ketsu Fan]

Hello,

I'm trying to separate digits from a large number, a date actually, into adjacent cells.

For example, in cell A1, I have the eight-digit figure "11252009" (MM/DD/YEAR, or Nov 25, 2009).

I would like to separate the 11252009 into separate cells.

If cell A1 is 11252009, then I want:
B1 to have 11 (month)
C1 to have 25 (date)
D1 to have 2009 (year)

Note: I also have seven-digit dates as well (3152009 = 3/15/2009).

Please help.

Cheers,
Ketsu

EggHeadCafe - Software Developer Portal of Choice
Separate ASP Code From HTML Content
http://www.eggheadcafe.com/tutorial...5-d298fa3477ec/separate-asp-code-from-ht.aspx

 

[T. Valko]

One way:

Month:

=MONTH(TEXT(A1,"00\/00\/0000"))

Day:

=DAY(TEXT(A1,"00\/00\/0000"))

Year:

=YEAR(TEXT(A1,"00\/00\/0000"))

Format each as General or Number

 

[Bernd P]

Hello,

Can you also have 6 digit dates like 112009 = 1-Jan-2009?

If yes you are lost:
1112009 can be 11-Jan-2009 as well as 1-Nov-2009.

Regards,
Bernd

 

[Ron Rosenfeld]

Hello,

I'm trying to separate digits from a large number, a date actually, into adjacent cells.

For example, in cell A1, I have the eight-digit figure "11252009" (MM/DD/YEAR, or Nov 25, 2009).

I would like to separate the 11252009 into separate cells.

If cell A1 is 11252009, then I want:
B1 to have 11 (month)
C1 to have 25 (date)
D1 to have 2009 (year)

Note: I also have seven-digit dates as well (3152009 = 3/15/2009).

Please help.

Cheers,
Ketsu

EggHeadCafe - Software Developer Portal of Choice
Separate ASP Code From HTML Content
http://www.eggheadcafe.com/tutorial...5-d298fa3477ec/separate-asp-code-from-ht.aspx

B1: =LEFT(TEXT(A1,"00000000"),2)
C1: =MID(TEXT(A1,"00000000"),3,2)
D1: =RIGHT(A1,4)

Note that the above values will be returned as TEXT. If you need them
converted to numbers, merely precede the above formulas with a double unary
(e.g. =--LEFT(TEXT... )

--ron

 

[Claus Busch]

Hello Ketsu,

Am Mon, 26 Oct 2009 21:41:27 -0700 schrieb Ketsu Fan:

I would like to separate the 11252009 into separate cells.

If cell A1 is 11252009, then I want:
B1 to have 11 (month)
C1 to have 25 (date)
D1 to have 2009 (year)

Note: I also have seven-digit dates as well (3152009 = 3/15/2009).

in B1: =INT(A1/1000000)
in C1: =INT(MOD(A1,1000000)/10000)
in D1: =MOD(A1,10000)


Mit freundlichen Grüssen
Claus Busch