Setting Cell Formats

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am putting a time format to a cell in excel. When I have users type in the
time like start and end times, the start time is always AM and the end time
is always PM however I cannot get the end time to default to PM. Is there a
way to default the cell to PM all the time with out setting the format to be
militaary time.

Thanks for your time and assistance
 
I am putting a time format to a cell in excel. When I have users type in the
time like start and end times, the start time is always AM and the end time
is always PM however I cannot get the end time to default to PM. Is there a
way to default the cell to PM all the time with out setting the format to be
militaary time.

Thanks for your time and assistance
Click to expand...

The time format only determines how the contents of the cell are DISPLAYED. It
does not affect the INPUT parser.

You will have to have your users enter the time in a format that Excel
understands for input. E.g.: 16:22 4:15 P etc.

Two alternatives:

1. Have them enter the time in one cell; but for your displays and
calculations use another column in which you have made the conversion by adding
0.5 (12 hrs) to the entry.

2. Use an event macro to do the conversion "in place" after the entry has been
made.

For display purposes, if you are using #1, you could use a custom format of
h:mm "PM". This could be confusing as it will always display "PM" even though
the actual entry might be in "AM".


--ron
 
You might try this:

Since XL *needs* the colon to recognize entries as time, use "Auto Correct"
to help in entering the colon with keystrokes from the num keypad.

<Tools> <AutoCorrect>

And enter 2 periods (dots - decimals) [ .. ] to be replaced with a colon [
: ].

Format is General,

You can now type
3..15 a
to display
3:15 AM

OR
10..30 p
to display
10:15 PM

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I am putting a time format to a cell in excel. When I have users type in
the
time like start and end times, the start time is always AM and the end time
is always PM however I cannot get the end time to default to PM. Is there a
way to default the cell to PM all the time with out setting the format to be
militaary time.

Thanks for your time and assistance
 
Typo ...

Of course,
10..30 p
WILL DISPLAY
10:30 PM
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

You might try this:

Since XL *needs* the colon to recognize entries as time, use "Auto Correct"
to help in entering the colon with keystrokes from the num keypad.

<Tools> <AutoCorrect>

And enter 2 periods (dots - decimals) [ .. ] to be replaced with a colon [
: ].

Format is General,

You can now type
3..15 a
to display
3:15 AM

OR
10..30 p
to display
10:15 PM

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I am putting a time format to a cell in excel. When I have users type in
the
time like start and end times, the start time is always AM and the end time
is always PM however I cannot get the end time to default to PM. Is there a
way to default the cell to PM all the time with out setting the format to be
militaary time.

Thanks for your time and assistance
 
Back
Top