Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
text to date format
I am exporting into Excel from the electronic medical record system,
AllScripts Professional EHR. There are 2 columns of dates which ultimately I need to subtract. One column of dates comes across just fine, it's obviously a date column. The other column of dates, when you click on a cell, NOTHING shows up in the white bar below the ribbon, however, there are dates that are left justified in the column, plain as day. My challenge is to turn these mysterious cells into dates so I can perform the subtraction. One more note, the column in question (along with the text column before it) does not show up on the screen after export unless I insert a column and then the text and "date" columns pop up on the screen. AllScripts says there are "special characters" there and has no real ideas for me. Thanks! Note: Version is 2007 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
text to date format
One frequent culprit is a non-breaking space, CHAR(160).
Try =SUBSTITUTE(A1,CHAR(160),"") After that you may need to convert text to columns to change to a number, or you may get away with doing the 2 steps together by using =--SUBSTITUTE(A1,CHAR(160),"") and formatting appropriately. If it isn't that character, you may need to check your text string to find out what the dodgy characters are. -- David Biddulph "EMREMEV" wrote in message ... I am exporting into Excel from the electronic medical record system, AllScripts Professional EHR. There are 2 columns of dates which ultimately I need to subtract. One column of dates comes across just fine, it's obviously a date column. The other column of dates, when you click on a cell, NOTHING shows up in the white bar below the ribbon, however, there are dates that are left justified in the column, plain as day. My challenge is to turn these mysterious cells into dates so I can perform the subtraction. One more note, the column in question (along with the text column before it) does not show up on the screen after export unless I insert a column and then the text and "date" columns pop up on the screen. AllScripts says there are "special characters" there and has no real ideas for me. Thanks! Note: Version is 2007 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
text to date format
Hi,
I can't really say what's happening, but try this for starters: The "white bar below the ribbon" is called the Formula Bar. Place your mouse along the bottom of the of this Formula Bar and when the mouse becomes a two headed arrow, drag down. It seem likely that the dates are preceeded by special characters, so you may now see them on the formula bar. To clean up this problem try =CLEAN(A1) this formula assumes that A1 has one of the offending dates. put this formula in a blank cell and see if that helps, if so copy the formula down, then convert the formula to values using Copy and Paste, Paste Values -- If this helps, please click the Yes button Cheers, Shane Devenshire "EMREMEV" wrote: I am exporting into Excel from the electronic medical record system, AllScripts Professional EHR. There are 2 columns of dates which ultimately I need to subtract. One column of dates comes across just fine, it's obviously a date column. The other column of dates, when you click on a cell, NOTHING shows up in the white bar below the ribbon, however, there are dates that are left justified in the column, plain as day. My challenge is to turn these mysterious cells into dates so I can perform the subtraction. One more note, the column in question (along with the text column before it) does not show up on the screen after export unless I insert a column and then the text and "date" columns pop up on the screen. AllScripts says there are "special characters" there and has no real ideas for me. Thanks! Note: Version is 2007 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
text to date format
Thanks for getting back to me, Shane and David.
Pulling down the formula bar was very helpful, now I know that there is a RETURN before the date. When I did CLEAN and COPY-PASTE SPECIAL-VALUES I got a text value. I can parse the text "date" but not sure how to put it back together. Thanks! "Shane Devenshire" wrote: Hi, I can't really say what's happening, but try this for starters: The "white bar below the ribbon" is called the Formula Bar. Place your mouse along the bottom of the of this Formula Bar and when the mouse becomes a two headed arrow, drag down. It seem likely that the dates are preceeded by special characters, so you may now see them on the formula bar. To clean up this problem try =CLEAN(A1) this formula assumes that A1 has one of the offending dates. put this formula in a blank cell and see if that helps, if so copy the formula down, then convert the formula to values using Copy and Paste, Paste Values -- If this helps, please click the Yes button Cheers, Shane Devenshire "EMREMEV" wrote: I am exporting into Excel from the electronic medical record system, AllScripts Professional EHR. There are 2 columns of dates which ultimately I need to subtract. One column of dates comes across just fine, it's obviously a date column. The other column of dates, when you click on a cell, NOTHING shows up in the white bar below the ribbon, however, there are dates that are left justified in the column, plain as day. My challenge is to turn these mysterious cells into dates so I can perform the subtraction. One more note, the column in question (along with the text column before it) does not show up on the screen after export unless I insert a column and then the text and "date" columns pop up on the screen. AllScripts says there are "special characters" there and has no real ideas for me. Thanks! Note: Version is 2007 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change Date Format to Specific Text Format When Copying | Excel Discussion (Misc queries) | |||
Convert date from text format to date format | Excel Discussion (Misc queries) | |||
Convert date + time text format to date format | Excel Worksheet Functions | |||
Concatenating a Text and a Date without losing orginal Date Format | Excel Discussion (Misc queries) | |||
Help: How do I convert a text date into a real date format | Excel Worksheet Functions |