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

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

"Mike" wrote in message
...
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

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

"Mike" wrote:

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

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

"Mike" wrote:

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

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

"Mike" wrote:

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

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.

"Mike" wrote:

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

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

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

"Mike" wrote:

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

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. :-(
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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

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

"Mike" wrote:

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

On Mon, 25 Jan 2010 08:41:02 -0800, Mike
wrote:

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

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

"Mike" wrote:

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

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

"Mike" wrote in message
...
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

"Mike" wrote:

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