fixing time column

  • Thread starter Thread starter Tracey
  • Start date Start date
T

Tracey

How can I extract/copy the time from 1 column to another and successfully
apply a time format of 00:00 to the new column/data?

I imported tab delimited data which contains 2 columns with time values.
The first time column successfully receives the time format 00:00 (I do this
so they are a uniform width).
However the 2nd time column does not receive the time format not and I
cannot "see" what is preventing the time format from being applied.

Any help will be appreciated.
Thanks, Tracey
 
What do the values in that second column look like?

Maybe excel can't recognize them as times.
 
some are 1 digit colon 2 digits and some are 2 digits colon 2 digits
I recognize them all as a time of day.
All I wanted to do is make them all look like 2 digits colon 2 digits even
if the hour is before 10 o'clock.
Tracey
 
The first thing I'd try is to select that column and then
Edit|replace
what: : (colon)
with: : (colon)

to see if excel would convert it to time.

If it did, then I'd format it the way I wanted.
format|cells|number tab|custom
hh:mm
(maybe)

If that didn't work, how about some examples of the problem values?
 
When I apply the format 00:00 to both column, only 1 reformats, the 2nd
column the single digit hours stay single digit when they should be padded
with a zero.

First column has 7:58 which converts to 07:58
Second column 8:03 does NOT convert to 08:03, but just displays 8:03
Sample:
13:36 7:47
16:12 8:03
07:58 17:32
07:58 17:43
12:31 17:31
12:32 8:00
12:32 8:03
12:33 8:00
12:34 7:56

When I move the cursor over the cell with "8:03" the cell in the formula bar
displays 8:03:00 AM so I know it recognizes the time.
I even copied and pasted the "8:03:00 AM" from the cell in the formula bar
into this email.

Just as an example, I want to format times with single digit hours (like
8:03) like 08:03 double digit with a leading zero.

Tracey
 
I'm guessing that the value looks like a time, but it isn't. There may be some
other characters in that cell--non-breaking spaces (HTML junk).

If you format the cell as General, does it change to a decimal smaller than
1--or does it still look like a time?

You can get a nice addin from Chip Pearson that'll show you each character in
the cell:
http://www.cpearson.com/excel/CellView.htm

And if it is non-breaking spaces (hex 160), then you could use a macro from
David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

And if you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

===
Did you try the edit|replace stuff? If there were extra spaces in the cell,
this might work ok.
When I apply the format 00:00 to both column, only 1 reformats, the 2nd
column the single digit hours stay single digit when they should be padded
with a zero.

First column has 7:58 which converts to 07:58
Second column 8:03 does NOT convert to 08:03, but just displays 8:03
Sample:
13:36 7:47
16:12 8:03
07:58 17:32
07:58 17:43
12:31 17:31
12:32 8:00
12:32 8:03
12:33 8:00
12:34 7:56

When I move the cursor over the cell with "8:03" the cell in the formula bar
displays 8:03:00 AM so I know it recognizes the time.
I even copied and pasted the "8:03:00 AM" from the cell in the formula bar
into this email.

Just as an example, I want to format times with single digit hours (like
8:03) like 08:03 double digit with a leading zero.

Tracey
Click to expand...
 
I used CellView and nothing but the viewable characters appear.
Changing to general does not change their appearance.
There are about 1500 rows and I guess Excel won't change the format if
there's a glitch in 1 of them.
Any other suggestions?
I can just right justify the column and be done with it, but I would like to
resolve it just in case something comes up in the future.
Tracey
 
If changing the format to General doesn't change the value to a number (not a
time), then the value isn't numeric (yet).

One more attempt:

Select an empty cell.
copy it
select your range of times
edit|paste special|check add
Reformat the range to show the time the way you like.
I used CellView and nothing but the viewable characters appear.
Changing to general does not change their appearance.
There are about 1500 rows and I guess Excel won't change the format if
there's a glitch in 1 of them.
Any other suggestions?
I can just right justify the column and be done with it, but I would like to
resolve it just in case something comes up in the future.
Tracey
Click to expand...
 
Thanks for your help.
I don't know what adding did to the data, but...
#1 I copied the cells just like you said.
#2 I tried formatting the new cells using time and it would NOT work.
#3 I tried using "CUSTOM" format hh:mm and it DID work!!!

Great !!!, but wait...

#4 Just for grins I tried "CUSTOM" format hh:mm (again, I tried it before)
on the original 2nd column of time and it still did NOT work.

Time and Custom did NOT work on the original 2nd column of time data, but
the Custom DID work on the ADDed data.
I don't know what ADDing does, but it somehow fixed the cell properties to
some degree!!!

Thinking out loud...
The cell properties for Time Format must be temperamental because:
Excel has always recognized all the time(s) in this spreadsheet because the
cell in the tool bar ALWAYS displayed the default format using "AM" or "PM"
as applicable when the cursor was at a cell with the time in it. If it could
not recognize the time data as time then why did it display "AM" or "PM" as
applicable? Single digit hours and 11 had AM and all other 2 digit hours had
PM (these are daytime hours)
Also Time formatted the 1st column (of time) correctly, but would not format
the 2nd

Again thanks for the reply!
Thanks, Tracey
 
The only thing I could think of is that the cells that really look like time
aren't. I was guessing that the value in the cell was a string--just like
typing a leading apostrophe will force the entry to be treated as text.

'January 12, 2004

(either with the leading apostrophe or typed into a cell that was already
formatted as Text will look like a date--but not treated as a date.)

One more test (if you're up for it).

Real dates/times in excel are just plain numbers.

Try this formula in a couple of empty cells:

=isnumber(a1)

And point at one of those cells that you know is ok. And point to a "bad" cell
in the other formula.

If you see True, then that cell should react to your formatting change.

If you see False, then that cell shouldn't react to your formatting change.

The stuff we tried (er, you tried) was supposed to convert the Text values to
Number values. I'm not sure why none of it worked.


Thanks for your help.
I don't know what adding did to the data, but...
#1 I copied the cells just like you said.
#2 I tried formatting the new cells using time and it would NOT work.
#3 I tried using "CUSTOM" format hh:mm and it DID work!!!

Great !!!, but wait...

#4 Just for grins I tried "CUSTOM" format hh:mm (again, I tried it before)
on the original 2nd column of time and it still did NOT work.

Time and Custom did NOT work on the original 2nd column of time data, but
the Custom DID work on the ADDed data.
I don't know what ADDing does, but it somehow fixed the cell properties to
some degree!!!

Thinking out loud...
The cell properties for Time Format must be temperamental because:
Excel has always recognized all the time(s) in this spreadsheet because the
cell in the tool bar ALWAYS displayed the default format using "AM" or "PM"
as applicable when the cursor was at a cell with the time in it. If it could
not recognize the time data as time then why did it display "AM" or "PM" as
applicable? Single digit hours and 11 had AM and all other 2 digit hours had
PM (these are daytime hours)
Also Time formatted the 1st column (of time) correctly, but would not format
the 2nd

Again thanks for the reply!
Thanks, Tracey
Click to expand...
 
Back
Top