Hi folks,
In an excel workbook (in excel 2002), I need to conatinate three columns from an excel one worksheet (Sheet1) into one column in another worksheet (Sheet2). The columns are PatientID (such as A04100108), VisitNumber (such as 06), and VisitDate (such as 7/23/2002).
I'm using the following formula in Sheet2:
=CONCATENATE(Sheet1!A1," ",Sheet1!B1," ",Sheet1!C1)
This does the job *except* for the fact that the 'VisitDate' is now displayed as a serial date number '37460', since the concatenate() function takes the original date format and converts it to a text string (which, for dates in excel means the serial date number). So, from the examples listed above, instead of a column containing the string "A04100108 06 7/23/2002" I get a column containing the string "A04100108 06 37460".
I need for that '37460' to be seen a a date in the final column. Does anyone know how to do this? If there were an excel function that could convert dates (serial date numbers, I suppose) to actual text string dates (not just for display, but to actually convert the number to a string) then I suppose I could convert the "VisitDate" column to such text string dates before concatenating and... walla, my problem would be solved.
Does anyone know of such a function, or of any other way to solve this problem? (I guess I could write my own if I knew VBA or something, huh?)
Thank you,
jay9333
In an excel workbook (in excel 2002), I need to conatinate three columns from an excel one worksheet (Sheet1) into one column in another worksheet (Sheet2). The columns are PatientID (such as A04100108), VisitNumber (such as 06), and VisitDate (such as 7/23/2002).
I'm using the following formula in Sheet2:
=CONCATENATE(Sheet1!A1," ",Sheet1!B1," ",Sheet1!C1)
This does the job *except* for the fact that the 'VisitDate' is now displayed as a serial date number '37460', since the concatenate() function takes the original date format and converts it to a text string (which, for dates in excel means the serial date number). So, from the examples listed above, instead of a column containing the string "A04100108 06 7/23/2002" I get a column containing the string "A04100108 06 37460".
I need for that '37460' to be seen a a date in the final column. Does anyone know how to do this? If there were an excel function that could convert dates (serial date numbers, I suppose) to actual text string dates (not just for display, but to actually convert the number to a string) then I suppose I could convert the "VisitDate" column to such text string dates before concatenating and... walla, my problem would be solved.
Does anyone know of such a function, or of any other way to solve this problem? (I guess I could write my own if I knew VBA or something, huh?)
Thank you,
jay9333
