Converting time question

[Kohn]

How can I convert time from a 12-hr clock to 24-hr clock?

Here's my situation:

I have a constant time in every cell column A (12:43:02) and I have a
variable time in column C (e.g., C2 has 12:43:25; C40 has 12:57:37). I
have a formula set up in column D to subtract cells in column A from
column C (e.g., =C2-A2 which returns 0:00:23)

My problem is when the times in column C go past 12:59:59 (e.g.,
1:00:12). This returns an error (because you can't have negative
time).

How can I convert time 1:00:12 (PM) to 13:00:12 ?? I need to do this
with a lot of cells, so simply retyping the values is not ideal.

Thank you so much for your help!

 

[Bob Greenblatt]

How can I convert time from a 12-hr clock to 24-hr clock?

Here's my situation:

I have a constant time in every cell column A (12:43:02) and I have a
variable time in column C (e.g., C2 has 12:43:25; C40 has 12:57:37). I
have a formula set up in column D to subtract cells in column A from
column C (e.g., =C2-A2 which returns 0:00:23)

My problem is when the times in column C go past 12:59:59 (e.g.,
1:00:12). This returns an error (because you can't have negative
time).

How can I convert time 1:00:12 (PM) to 13:00:12 ?? I need to do this
with a lot of cells, so simply retyping the values is not ideal.

Thank you so much for your help!

You can do this with the format of the cell. Enter the value as either
13:00:12, or 1:00:12 PM Your formula will still work fine.

 

[Kohn]

Yeah, I know about that. I just realized that my problem is that
Excel is not recognizing the values in column C as time. I extracted
these times from a long string using Text-to-Columns. Unfortunately,
I'm unable to get Excel to recognize these values as time, even by
changing the cell format.

 

[Kohn]

And I'm an idiot....there was an extra space after the time.

Thanks for your help though!

 

[Kohn]

And I'm an idiot....there was an extra space after the time.

Thanks for your help though!

Although that is a bug that the MS Excel team should fix.
For some reason when using Text-to-Columns, it'll add an extra space
after where the line is supposed to cut the string

 

[CyberTaz]

Hi Nicholas;

<snip>

Excel is not recognizing the values in column C as time. I extracted
these times from a long string using Text-to-Columns

<snip>

That's most likely where the spaces came from in the first place

Often what appears to be plain text will include hidden characters which may
very well be interpreted as spaces, but I've never known Excel to
arbitrarily insert spaces when using Text to Columns.

In fact, if you type something such as: 12:15:30 11:45:21 into a cell then
use TtC you'll see that Excel actually does drop the space & properly
interprets the 2 time values. Likewise, you can copy: 4:30:15, 13:25:30 from
a text document & TtC will enable distinguishing the times based on the
comma & drop the space. Additionally, if you copy something such as 12:15:32
from a text source & intentionally include a space at the end Excel will
ignore the space when you paste into a cell & format the cell as though you
had accurately typed the data.

Regards |:>)
Bob Jones
[MVP] Office:Mac