dates in excel

[auzzie]

I have exported some data from a payroll package, but the dates of birth have been exported without the separator and backwards ie. 98910120: 2/10/1989. How can i convert it to the correct style? I have tried allsorts of formatting but it just wont have it. Any suggestions please?

[Cockney_si]

You'll probably have to re-import them and then change them within the payroll package. Or re-export them again and make sure the seperators sre there.

Sounds like a tricky one and I've seen a few in my time ;o)

[kempie]

You can reverse the figures into date format using

=DATEVALUE(MID(A1,8,1)&MID(A1,7,1)&"/"&MID(A1,6,1)&MID(A1,5,1)&"/"&MID(A1,4,1)&MID(A1,3,1)&MID(A1,2,1)&MID(A1,1,1))

(one line with no gaps)

where A1 indicates the cell of the data to convert.

[auzzie]

Thanks to both
Kempie, i have a #value come up in my cell. It gave me an option to correct the formula with an extra ) at the end. I accepted it and it came up with the error message. Wouldn't work if i didn't accept it. In A1 i have 98910120 (02.10.1989) it is under the general format. and i entered the formula in B1. If i change the format to date it comes up with loads of ########### ! Where am i going wrong?

[kempie]

AnswerBank has placed 2 spaces in the formula { before (A1,5,1) and before the final ) } which when removed is accepted as a valid entry and works properly in Excel 2000.

Did you copy/paste or type the formula in?

[auzzie]

I typed it in, no spaces

[kempie]

A1 should be formatted as General while B1 is the cell to format as Date.

[auzzie]

Thanx for all your help Kempie. I have formatted as you said i still get the #value error. When i go to evaluate it says:

Sheet1!$B$1
Evaluation = DATEVALUE("0/2/10/1910120")
The next evaluation will result in an error.

......and it does #Value!!

I have Excel 2002, don't know if that makes a difference. One of the things i did notice in your formula, you have a &"/" in between every MID except between 8 & 7, i have put one in just in case but it still does not work!!

[kempie]

There are only two "/" in the formula - after 7 and 5.

Each MID is one character of the date and the "/" insert the date separators.

8 7 / 6 5 / 4 3 2 1 ... (the MIDs and "/"s in the formula)
d d / m m / y y y y ... (the resulting date)

[auzzie]

Yeehaa, it works. See what happens when you have too many slashes!!!

Many thanks Kempie for your trouble & patience!