You could put it all into one formula, like this:

=Date(A1,LEFT(B1,1+(LEN(B1)3)),RIGHT(B1,2))

If you put this in C1 and format it as a date, then you can copy it
down for as many entries as you have in column B.

Hope this helps.

Pete

On Mar 11, 6:59*pm, dslocum
wrote:
Ok, what I am trying to do is this. *My client is a Dr. and he is trying
to get his patient data into Excel so that he can then import the data
into Outlook and from there he plans to synch it to his cell phone.
Each row of data in the spreadsheet is a patient's record, contact
information, date of birth, etc. *This is all fine and the import works
perfectly except that his data base outputs the patient's birthdate into
two separate cells; one for the birth year and the other for the
month/day. *Unfortunately, Excel truncated the preceeding zero (0) for
the months of January through September (i.e. 01 became 1, 06 became 6)
and to reformat the cell into a Date format returns a bogus date. *I am
the closest thing to an Excel expert that he has so he asked me for
help. *After researching I came up with the following: *Assume that
birthyears are in Column A and birth month/day are in Column B for each
patient.

I inserted three new columns, column C, D and E; and entered the
following formula into C1: =IF(LEN(B1)=3,Left(B1,1),Left(B1,2)
This gave me the correct month.

I inserted the following into cell D1: =Right(B1,2)
This gave me the correct day.

I inserted the following into cell E1: =Date(A1,C1,D1)
This gave me the birthdate in the correct format.

This works but seems like a lot of steps since the Dr. wishes to do
this a couple of times a week or more. *

Is there a more efficient way to do this? *Ron, when I used your
formulas I got #value and #Num errors.

denny

--
dslocum