PDA

View Full Version : Split date into part



sujittalukde
06-25-2007, 05:55 AM
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
06-25-2007, 06:15 AM
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
06-25-2007, 06:27 AM
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
06-25-2007, 06:34 AM
Hi,

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

Lynnnow

sujittalukde
06-25-2007, 06:36 AM
Yeah thats typing mistake. it is $A$1. Thanks for pointing out the same.

mdmackillop
06-25-2007, 10:42 AM
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