Manipulating Data Dump in EXCEL - HELP

[stlrain95]

I am trying to format a column in Excel with a date/time, but the format will not work due to a "'" before each date.
How can I eliminate this character without doing a right justify?

example: '3/31/2004 2:40:53 PM

Thanks,

 

[xlbo]

Try either
Tools>Options>Transition and uncheck "Transition Navigation Keys"
Or - if it is not checked

enter a 1 into any blank cell and COPY it

select all your dates

Go Edit>Pastespecial
Tick values and Multiply

Format the cells as you wish

This is known as the "Times 1 fix"

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

WHY are you using a ' to make the value a STRING???

'3/31/2004 2:40:53 PM is NOT a DATE/TIME!

3/31/2004 2:40:53 PM IS a DATE/TIME, which oddly enough in Excel, is REALLY a NUMBER -- 38077.61172

You get the FORMAT that you want by menu item Format/Cell/Number Tab and select a STOCK Date or Time Format or Create a CUSTOM format.

If I enter 3/31/2004 2:40:53 PM, it can be formatted, for instance...
[tt]
Display Format
Wednesday -- "dddd"
Wed, Mar 31, 2004 14:40:53 -- "ddd, mmm dd, yyyy hh:mm:ss"
[/tt]
SAME DATE/TIME value 38077.61172 (BTW the GENERAL Format)

Get rid of the TIC and use REAL Date/Time!!!






Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[stlrain95]

Skip,
This was a data dump. it assigned the "'" to the field. I am trying to eliminate that "'"
I understand the nature of the Date/time...but need to get it to that format.

 

[SkipVought]

use xlbo's suggestion

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xlbo]

My answer still stands but from the info given, seems to me you need the Times 1 Fix

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[stlrain95]

Too easy...it worked.

BTW, I did try the CLEAN function, but didn't seem to work either.

 

[xlbo]

nope - that's cos your "Dates" are being treated as TEXT - that's what the ' will do

The times 1 fix converts textual values to true values

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[01Patrik]

Why don't you simply replace (ctrl + H)... works like a clock...



// Patrik