converting 24 hour time to 12 hour time

[jr0410]

Using Excel 2000, when our truck drivers fill in time sheets for when they
deliver loads, they use 24 hour time, such as "1715" (without a colon). I
would like to enter that number in a column and have Excel convert "1715",
for example, into "5:15PM". can this be done without me having to put a
colon in the number (17:15)?

 

[Bernard Rey]

jr0410 :

Using Excel 2000, when our truck drivers fill in time sheets for when they
deliver loads, they use 24 hour time, such as "1715" (without a colon). I
would like to enter that number in a column and have Excel convert "1715",
for example, into "5:15PM". can this be done without me having to put a
colon in the number (17:15)?

One way could be to put this formula in cell B2:

=TIME(LEFT(A2;LEN(A2)-2);(RIGHT(A2;2));0)

This will work if the "1715" number has been entered in cell A2, of course,
and you may have to adapt it to your situation.

 

[jr0410]

Bernard: I receive a formula error message after entering the formula into
the worksheet!

 

[jpdphd]

Try changing the semicolons ( to commas (,).

Bernard: I receive a formula error message after entering the formula into
the worksheet!

 

[JE McGimpsey]

Using Excel 2000, when our truck drivers fill in time sheets for when they
deliver loads, they use 24 hour time, such as "1715" (without a colon). I
would like to enter that number in a column and have Excel convert "1715",
for example, into "5:15PM". can this be done without me having to put a
colon in the number (17:15)?

For a way, using an event macro, to make the change automatic, take a
look here:

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

 

[Bernard Rey]

Yes, jpdphd is right, sorry about that mistake (I forgot to change to US
settings):

=TIME(LEFT(A2,LEN(A2)-2),(RIGHT(A2,2)),0)


Should be right...

--
Bernard Rey - Toulouse / France
MVP - office:macintosh http://faq.office.macintosh.free.fr



jpdphd :

 

[dk_]

For a way, using an event macro, to make the change automatic, take a
look here:

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

The code on this link (above) will work perfectly. I am currently doing
exactly the same thing.

This works when entering the times, like "1715". The cell is formatted
in an AM/PM format, and it will display 5:15 PM.

Brilliant trick.

-DK

 

[jr0410]

Many Thanks...it did the trick!

Try changing the semicolons ( to commas (,).

 

[jr0410]

Thanks for the tip....I usually mess up when I hit ";" rather than ":" !!!!

 

[jr0410]

Thanks...it goes to show that I have a lot to learn where Excel is concerned!

 

[jr0410]

Thanks...it goes to show that I have a lot to learn where Excel is concerned!