Convert a text field to a date
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? -- dslocum |
Convert a text field to a date
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) "dslocum" wrote in message ... 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? -- dslocum |
Convert a text field to a date
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) "dslocum" wrote in message ... 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? -- dslocum |
Convert a text field to a date
On Tue, 11 Mar 2008 15:40:50 +0000, dslocum
wrote: 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 |
Convert a text field to a date
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 |
Convert a text field to a date
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 |
Convert a text field to a date
On Tue, 11 Mar 2008 18:59:47 +0000, dslocum
wrote: 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 |
All times are GMT +1. The time now is 02:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com