Convert a text field to a date

[dslocum]

I have researched this forum and have most of my problem solved. My
customer has two columns of data representing a client's birthday; A1
has the birth year formatted as yyyy. A2 has the birth month and day
formatted as mm/dd. Here's the problem, A2 is formatted as "General"
and therefore truncated 0827 (Aug 27) as 827. I wish to use the =Date
function to basically concantenate these cells but I need to know how
to convert 827 into 0827 so that I might use the =Left(A2,2) and
=Right(A2,2) to break out the month and day. Am I heading in the right
direction?

 

[Ron Coderre]

Try this:

=--(TEXT(A2,"0\/00\/")&B2)
format that cell as a DATE

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Ron Coderre]

Obviousely (hopefully), I mixed up the cell references.

The formula to convert A1 and A2 to a date should be:
=--(TEXT(A2,"0\/00\/")&A1)

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Ron Rosenfeld]

I have researched this forum and have most of my problem solved. My
customer has two columns of data representing a client's birthday; A1
has the birth year formatted as yyyy. A2 has the birth month and day
formatted as mm/dd. Here's the problem, A2 is formatted as "General"
and therefore truncated 0827 (Aug 27) as 827. I wish to use the =Date
function to basically concantenate these cells but I need to know how
to convert 827 into 0827 so that I might use the =Left(A2,2) and
=Right(A2,2) to break out the month and day. Am I heading in the right
direction?

=DATE(A1,INT(A2/100),MOD(A2/100,1)*100)

Although, if by columns you really mean columns, and not rows as you gave in
your example, then:

=DATE(A1,INT(B1/100),MOD(B1/100,1)*100)

--ron

 

[dslocum]

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

 

[Pete_UK]

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

 

[Ron Rosenfeld]

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

If you got those errors, then the values you are seeing in A1 and B1 are not
the same as what is there.

For example, if

A1: 2008
B1: 828

Then the formula

=DATE(A1,INT(B1/100),MOD(B1/100,1)*100) will give the date of 8/28/2008 (if
properly formatted) and not an error.

We could spend a lot of time trying to figure out what is really in there.
Commonly there is a <nbsp> which could be stripped off.

Or we could apply a formula to be sure we pick out the numbers only.

I would just go with what you have that works.

"Perfect is the enemy of good enough"
--ron