Hi,

I have a cell which is formatted to "04:30 PM" but need it to read like a
24hr clock,(ie 16:00) I've tried (re)formatting it using 'number-time-type'
but with no joy. Is there a formula which can convert please?

Thanks in advance,

AW

Format Cells... Number Custom [h]:mm
--
Gary''s Student - gsnu200758

Thanks for the swift reply Gary.

Yeah I had already done this format but no joy. My time is entered as
'12:30 PM (with the apostrophe at the start), will this affect the outcome?

Thanks,

AW

"Gary''s Student" wrote:

Format Cells... Number Custom [h]:mm

Yes.

Drop the apostrophe first
--
Gary''s Student - gsnu200758


"ArcticWolf" wrote:

Thanks for the swift reply Gary.

Yeah I had already done this format but no joy. My time is entered as
'12:30 PM (with the apostrophe at the start), will this affect the outcome?

Thanks,

AW

"Gary''s Student" wrote:

Format Cells... Number Custom [h]:mm

"ArcticWolf" wrote in message
...
Thanks for the swift reply Gary.

Yeah I had already done this format but no joy. My time is entered as
'12:30 PM (with the apostrophe at the start), will this affect the
outcome?

Thanks,

AW

"Gary''s Student" wrote:

Format Cells... Number Custom [h]:mm


Anything entered with an apostrophe in front is text. So you don't have a
time. No change of format will alter this.

Your question is therefore exactly like saying you have "abcdef" in a cell
and you want "ghijkl". '4.30 PM and '16:30 bear no relation to each other.

If you have a lot of these to do, then highlight the column and click
on Data | Text-to-columns. Click Finish on the first panel - that is
usually sufficient. Then re-apply your required format if necessary.

Hope this helps.

Pete

On Nov 21, 4:59 pm, Gary''s Student
wrote:
Yes.

Drop the apostrophe first
--
Gary''s Student - gsnu200758



"ArcticWolf" wrote:
Thanks for the swift reply Gary.

Yeah I had already done this format but no joy. My time is entered as
'12:30 PM (with the apostrophe at the start), will this affect the outcome?

Thanks,

AW

"Gary''s Student" wrote:

Format Cells... Number Custom [h]:mm- Hide quoted text -

- Show quoted text -

excel-ent suggestion
--
Gary''s Student - gsnu200758


"Pete_UK" wrote:

If you have a lot of these to do, then highlight the column and click
on Data | Text-to-columns. Click Finish on the first panel - that is
usually sufficient. Then re-apply your required format if necessary.

Hope this helps.

Pete

On Nov 21, 4:59 pm, Gary''s Student
wrote:
Yes.

Drop the apostrophe first
--
Gary''s Student - gsnu200758



"ArcticWolf" wrote:
Thanks for the swift reply Gary.

Yeah I had already done this format but no joy. My time is entered as
'12:30 PM (with the apostrophe at the start), will this affect the outcome?

Thanks,

AW

"Gary''s Student" wrote:

Format Cells... Number Custom [h]:mm- Hide quoted text -

- Show quoted text -

I am trying to convert whole numbers like 2300 to 23:00. I already have about
600 cells with the whole numbers and was hoping there was a way to convert
them to the 23:00 where a formula would automatically insert the : (colon)
I have tried several of the suggestions as listed in this positng, with no
good results...pls help

"Stephen" wrote:

"ArcticWolf" wrote in message
...
Thanks for the swift reply Gary.

Yeah I had already done this format but no joy. My time is entered as
'12:30 PM (with the apostrophe at the start), will this affect the
outcome?

Thanks,

AW

"Gary''s Student" wrote:

Format Cells... Number Custom [h]:mm


Anything entered with an apostrophe in front is text. So you don't have a
time. No change of format will alter this.

Your question is therefore exactly like saying you have "abcdef" in a cell
and you want "ghijkl". '4.30 PM and '16:30 bear no relation to each other.

Please do not multipost, I gave you the answer already to your earlier post

http://www.cpearson.com/excel/DateTimeEntry.htm


--


Regards,


Peo Sjoblom


"jshehan" wrote in message
...
I am trying to convert whole numbers like 2300 to 23:00. I already have
about
600 cells with the whole numbers and was hoping there was a way to convert
them to the 23:00 where a formula would automatically insert the : (colon)
I have tried several of the suggestions as listed in this positng, with no
good results...pls help

"Stephen" wrote:

"ArcticWolf" wrote in message
...
Thanks for the swift reply Gary.

Yeah I had already done this format but no joy. My time is entered as
'12:30 PM (with the apostrophe at the start), will this affect the
outcome?

Thanks,

AW

"Gary''s Student" wrote:

Format Cells... Number Custom [h]:mm


Anything entered with an apostrophe in front is text. So you don't have a
time. No change of format will alter this.

Your question is therefore exactly like saying you have "abcdef" in a
cell
and you want "ghijkl". '4.30 PM and '16:30 bear no relation to each
other.