text to date format

[EMREMEV]

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

 

[David Biddulph]

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.

 

[Shane Devenshire]

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

 

[EMREMEV]

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!