Deliberately ambiguous time format? (No AM/PM)
 

When I type a time such as 5:30 PM or 17:30 in a cell, I want it to
display ambiguously as 5:30 without AM or PM. When I copy and paste
the cell elsewhere, I want Excel to know it's PM.

Excel doesn't seem to accommodate this simple alternate format. The
custom formatting I've tried seems to limit me to either a 24-hour
clock or AM/PM, as if someone thought that an ambiguous format would
have no possible use. (I could easily use colors or bold typeface
instead of showing AM/PM, for instance.)

Is there in fact some way to do this?

Dan Williams
danwPlanet

Deliberately ambiguous time format? (No AM/PM)
 

Maybe you can use a helper cell (and hide the original cell???)

=LEFT(TEXT(A1,"hh:mm AM/PM"),5)

Then use format|conditional formatting to determine the boldness--but look at
that original cell:

Format|conditional formatting
formula is:
=(A1=0.5)

This cell is just text, so you won't want to use it other
calculations--especially since you're losing any PM's.

wrote:

When I type a time such as 5:30 PM or 17:30 in a cell, I want it to
display ambiguously as 5:30 without AM or PM. When I copy and paste
the cell elsewhere, I want Excel to know it's PM.

Excel doesn't seem to accommodate this simple alternate format. The
custom formatting I've tried seems to limit me to either a 24-hour
clock or AM/PM, as if someone thought that an ambiguous format would
have no possible use. (I could easily use colors or bold typeface
instead of showing AM/PM, for instance.)

Is there in fact some way to do this?

Dan Williams
danwPlanet

--

Dave Peterson

Deliberately ambiguous time format? (No AM/PM)
 

I know the workarounds, but I mostly want to confirm that the answer is
no first.
Dan

Dave Peterson wrote:
Maybe you can use a helper cell (and hide the original cell???)

=LEFT(TEXT(A1,"hh:mm AM/PM"),5)

Then use format|conditional formatting to determine the boldness--but look at
that original cell:

Format|conditional formatting
formula is:
=(A1=0.5)

This cell is just text, so you won't want to use it other
calculations--especially since you're losing any PM's.

wrote:

When I type a time such as 5:30 PM or 17:30 in a cell, I want it to
display ambiguously as 5:30 without AM or PM. When I copy and paste
the cell elsewhere, I want Excel to know it's PM.

Excel doesn't seem to accommodate this simple alternate format. The
custom formatting I've tried seems to limit me to either a 24-hour
clock or AM/PM, as if someone thought that an ambiguous format would
have no possible use. (I could easily use colors or bold typeface
instead of showing AM/PM, for instance.)

Is there in fact some way to do this?

Dan Williams
danwPlanet

--

Dave Peterson

Deliberately ambiguous time format? (No AM/PM)
 

I've never been able to do what you want.



Dan Williams wrote:

I know the workarounds, but I mostly want to confirm that the answer is
no first.
Dan

Dave Peterson wrote:
Maybe you can use a helper cell (and hide the original cell???)

=LEFT(TEXT(A1,"hh:mm AM/PM"),5)

Then use format|conditional formatting to determine the boldness--but look at
that original cell:

Format|conditional formatting
formula is:
=(A1=0.5)

This cell is just text, so you won't want to use it other
calculations--especially since you're losing any PM's.

wrote:

When I type a time such as 5:30 PM or 17:30 in a cell, I want it to
display ambiguously as 5:30 without AM or PM. When I copy and paste
the cell elsewhere, I want Excel to know it's PM.

Excel doesn't seem to accommodate this simple alternate format. The
custom formatting I've tried seems to limit me to either a 24-hour
clock or AM/PM, as if someone thought that an ambiguous format would
have no possible use. (I could easily use colors or bold typeface
instead of showing AM/PM, for instance.)

Is there in fact some way to do this?

Dan Williams
danwPlanet

--

Dave Peterson

--

Dave Peterson