Entering a time into excel

[Mike]

I am trying to enter a time in military time ie: 1500 for 3:00 PM. Everytime
it puts 0:00. I have tried formatting the cell everyway possible. I don't
need a date stamp just the time for time cards. I would like the end result
to display standard time 3:00 PM. Any help would be greatly apreciated.

Thanks in advance.
Mike

 

[Fred Smith]

In order for Excel to recognize input as a time (as opposed to the number
1,500), you must enter a colon. You can enter 15:00, and it will be accepted
as 3:00 pm (regardless of the formatting). You can then format the cell as
hhmm, which will display the way you want.

Regards,
Fred

 

[Arceedee]

In 2007.

Highlight and r click then format cell. Seelct time 01:30:55PM OK

Go into format cell again but this time select custom then hh:mm

 

[Arceedee]

Ignore last message - doesn't work when I try it again. The first bit does
so you get hh mm ss. Will that do?

 

[Eduardo]

hI,
right click on the cell, format cells, time, choose the option 1:30:55 PM,
then each time you enter 15:00 it will show as 3:00:00 PM

 

[PJ]

Hi Mike

If you enter the 'miltary' time as 15:00 (i.e. with a colon between the
hours and minutes) and format the cell to show AM/PM time, this should do
what you want.

 

[David Biddulph]

If the OP has put the number 1500 into the cell, changing the format of the
display will not change the stored number from 1500 to 15:00

If a range of numbers have already been stored, the best bet may be to put
the number 2400 into a spare cell, copy, then select the range of "times"
and do Edit/ Paste Special/ Divide, then format the cells as time to suit.

 

[David Biddulph]

Ignore that suggestion; I was half asleep.
It would be OK for whole numbers of hours, but 1510 gets turned into 15:06.
I wondered why I hadn't thought of such a simple way in the past if it had
been that easy. :-(

 

[Ron Rosenfeld]

I am trying to enter a time in military time ie: 1500 for 3:00 PM. Everytime
it puts 0:00. I have tried formatting the cell everyway possible. I don't
need a date stamp just the time for time cards. I would like the end result
to display standard time 3:00 PM. Any help would be greatly apreciated.

Thanks in advance.
Mike

Formatting for dates and times ONLY affects how the contents of the cell is
DISPLAYED. It does NOT affect how the input to that cell is parsed.

Excel stores dates/times as days and fractions of a day, with 1 = 1 Jan 1900.

So entering 1500 leads Excel to interpret that as the 1500th day since 1 Jan
1900, at 12 midnight. Or 8 Feb 1904 12:00 AM.

To have Excel interpret your entries as time, you will need to either
Use the colon separator (e.g. enter as 15:00)
use VBA event code,
or enter the times in one cell and use a different cell to display the
data.

Which would you prefer.
--ron

 

[Mike]

Thank you all that responded. In an effort to make data entry fast and
painless, we were looking for a way to eliminate the colon while still
achieving the same end result. I truly appreciate the time and effort
everyone took to help.

Mike

 

[Fred Smith]

The way would do it is to do the conversion in another cell (probably
adjacent column).

If 1500 is in A1, you can convert this to a time of 15:00 by:
=time(int(a1/100),mod(a1,100),0)

Regards,
Fred