Split date into part

[sujittalukde]

I want to split a dte into parts. For eg.,

Date in A1: 25/06/2007

Required:
In A2 : 25
in A3 : 06
In A4 : 2007

Though the case is very simple, but I could not trigger it.

[lynnnow]

Hi,

Can you convert the date into mm/dd/yyyy formats and work with it. coz the solution is pretty easy that way. but if not, then, let's try something...

are all the dates in dd/mm/yyyy format? if yes, then here is your formula instead of a macro.

in the three columns where you want the date to be separated, you can insert the following formulae:

A2: =LEFT(A1,2)
A3: =MID(A1,4,2)
A4: =RIGHT(A1,4)

If there is any other format, this will change to some extent.

If you can change the date to mm/dd/yyyy format, then the formulae you need to insert is:

A2: =Month(A1)
A3: =Day(A1)
A4: =Year(A1)

HTH

Lynnnow
Mumbai, India

[sujittalukde]

Thanks for the reply but I cannot change to mm/dd/yyyy. Solution provided by you for the dd/mm/yyyy format, it is not giving a correct result. So i have changed the same to this and working:
=LEFT(TEXT($A$1,"dd/mm/yyyy"),2)
=MID(TEXT($A$11,"dd/mm/yyyy"),4,2)
=RIGHT(TEXT($A$1,"dd/mm/yyyy"),4)

[lynnnow]

Hi,

Why does the MID formula have $A$11 in it? guess it is just a typo. Anyway, woteva works for u.

Lynnnow

[sujittalukde]

Yeah thats typing mistake. it is $A$1. Thanks for pointing out the same.

[mdmackillop]

If your format is dd/mm/yy then =Day(A1), =month(A1), =Year(A1) should work. You'll need to format your cell though to get 06