04:30 PM to 16:30

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Yes.

Drop the apostrophe first
--
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

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

 

[Stephen]

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

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.

 

[Pete_UK]

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

Yes.

Drop the apostrophe first
--
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

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

- Show quoted text -

 

[Guest]

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

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

Yes.

Drop the apostrophe first
--
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?


"Gary''s Student" wrote:

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

- Show quoted text -

 

[jshehan]

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

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

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.

 

[Peo Sjoblom]

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

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


--


Regards,


Peo Sjoblom

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

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

:

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.