04:30 PM to 16:30

A

ArcticWolf

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
 
A

ArcticWolf

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
 
G

Gary''s Student

Yes.

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


ArcticWolf said:
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 said:
Format > Cells... > Number > Custom > [h]:mm
 
S

Stephen

ArcticWolf said:
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 said:
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.
 
P

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



ArcticWolf said:
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?

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

- Show quoted text -
 
G

Gary''s Student

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


Pete_UK said:
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



ArcticWolf said:
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 -
 
J

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

Stephen said:
ArcticWolf said:
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 said:
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.
 
P

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


jshehan said:
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 said:
ArcticWolf said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top