Time Formating

M

Marianne

I have created an excel spreadsheet to calculate hours worked. When entering
the start and end times, the formating automatically assumes an AM. So if I
type "5:00" the cell formats it as "05:00 AM". But if I want a PM hour
entered such as 5:00 PM, I have to enter the P. Is there some way to format
the "End Time" cells so that it assumes a PM, unless an AM is entered?

I would appreciate any suggestions.

Marianne Castrovince
 
R

Ron Rosenfeld

I have created an excel spreadsheet to calculate hours worked. When entering
the start and end times, the formating automatically assumes an AM. So if I
type "5:00" the cell formats it as "05:00 AM". But if I want a PM hour
entered such as 5:00 PM, I have to enter the P. Is there some way to format
the "End Time" cells so that it assumes a PM, unless an AM is entered?

I would appreciate any suggestions.

Marianne Castrovince
Click to expand...

Formatting only affects how times are DISPLAYED. Formatting has NO effect on
how Excel parses entered time.

To enter 5:00 PM your options (without using VBA) include:

5:00 P
5:00 PM
5 P
(note the space after the number)

17:00


There may be other options, but I'm not certain.



--ron
 
B

Bernie Deitrick

Marianne,

No. You could use an event code, but then you would have to pre-format the cell as text to be able
to capture your actual input.

Or you could type the number as 17:00.

HTH,
Bernie
MS Excel MVP
 
Top