I need to change 4/21/1998 dates to 21/4/1998 HOW?

[ROn Stalenberg]

Having extracted data from an uncooperative piece of software, it gave me the
dates of birth in the format: 4/21/1998. I need to calculate the age of these
people -THis can be done using DATEDIF(F2,NOW(),"y")&" years,
"&DATEDIF(F2,NOW(),"ym")&" months" - - but seems the date format required
for the calculation is 21/4/1998.

How can I either fix the formula or change the dates into the required format?

 

[Ashish Mathur]

Hi,

Select the cell and go to Data > Text to columns. Click on Next twice and
on the third screen, under the column data format, select MDY. Now click
finish.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Bob Phillips]

Try this

=DATE(RIGHT(F2,4),LEFT(F2,FIND("/",F2)-1),MID(F2,FIND("/",F2)+1,FIND("/",F2,FIND("/",F2)+1)-(FIND("/",F2)+1)))

 

[ROn Stalenberg]

NOPE - Arthur.
Doesn't work Seems the formatting of the original date data is totally
inconsistent. The second hint from Bob Phillips works for SOME of the data,
but not all - Gives me a "VALUE" error

Ron

 

[ROn Stalenberg]

Thanks Bob,
That works for SOME of the data, but not all. Have tried the "Format
/Cells/Date/ method to change the date form at of the data to lots of
different fomats, but nothing seems to change the format of the original
data. Some of it even has spaces between digits and / of the dates.

Ron

 

[ROn Stalenberg]

MORE INFORMATION BOB:

The dates your formula DOESN'T work on are those that convert to a straight
numerical value when i change the format of the dates to text. It DOES work
for those dates that remain as dates when i format the column as text

Does that help?

Ron

 

[Bob Phillips]

His name is Ashish!

 

[Bob Phillips]

Can you give examples of working and not?