Excel should be able to format 12-hour times without am/pm

[Guest]

Being in the business of producing railway timetables, I'd like Excel to be
able to format times in a 12-hour format *without* having am/pm after each
time. In timetables, the am/pm is normally shown at the top of the column of
times, not against each time.

(I'm currently using Excel 2003)

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...c-74e229479428&dg=microsoft.public.excel.misc

 

[Leith Ross]

This is a good case for why macro code exists. Here a simple UDF woul
solve the problem. One that many members of the various news an
support groups could easily code. It's difficult to imagine Microsof
making such a consession to please such a small group who either isn'
aware of or is choosing to not use the resources available to them.

Leith Ros

 

[Guest]

This is a good case for why macro code exists. Here a simple UDF would
solve the problem.

UDF = User-defined-function?

In order for a function result to display in a 12-hour time format, it would
have to return a string, whereas what I'm asking for is a way to format a
time, which can be added to to produce the next time.

One that many members of the various news and
support groups could easily code.

As could I if that would solve the problem. In fact I could do it (perhaps
less efficiently) with built-in functions, but with the same problem of it
returning text, not a time.

It's difficult to imagine Microsoft
making such a consession to please such a small group who either isn't
aware of or is choosing to not use the resources available to them.

Whilst *making* such timetables may not be that large a group, I would have
thought that the ability to format times in this way would me no smaller a
group than many others that are catered for. There are many features in
Office that have me wondering "who on Earth would use that?", but presumably
there is either a bigger demand than I am aware of, or it was easy enough to
code for anyway for completeness.

Beyond that, as I said, it is not a case of not being aware of what's
available or choosing not to use them.

And clearly Microsoft is not going to go to a lot of effort for a few
people, but it would be nice to be able to post a suggestion without having
it immediately dismissed on the assumption that this is the case here.

 

[Roger Govier]

Hi Philip

The problem lies with carrying out calculations. If one is not using
military time, then how does one know whether 5:00 minus 3:00 is 2 hours
or 14 hours?
I understand your requirement however, and the way I would tackle it
would be simply to have 2 extra columns (hidden if required) which held
my times in military format and which I would use for any calculations.
The other two columns, which are the ones I would display, would be
formatted as just hh:mm
In the am column it would be just =A1 (or the cell containing the
military am time)
In the pm column it would be =A1-TIME(12,0,0)

 

[Guest]

Hi Philip

The problem lies with carrying out calculations. If one is not using
military time, then how does one know whether 5:00 minus 3:00 is 2 hours
or 14 hours?

When Excel formats times as 12-hour times, the underlying values are still a
full date/time value (including, if necessary, past midnight, being 1 +
<fractional time part>). So Excel knowing is not the problem.

The human readers of the times know which it is by an "a.m." or "p.m." at
the top of the column of times (see
http://www.connexmelbourne.com.au/trip_timetable/timetable/full/alameinUMF.asp for an example).

I understand your requirement however, and the way I would tackle it
would be simply to have 2 extra columns (hidden if required) which held
my times in military format and which I would use for any calculations.
The other two columns, which are the ones I would display, would be
formatted as just hh:mm
In the am column it would be just =A1 (or the cell containing the
military am time)
In the pm column it would be =A1-TIME(12,0,0)

I didn't mean to give the impression that I could find no way of doing it.
I can, and I have done it by having hidden columns with the values. In fact
only one extra column, not two, by simply having a formula to convert the
time to text, then strip off the am/pm characters at the end. And two
visible columns wouldn't work layout-wise (see the link).

Even doing it your way only one extra column is required, simply by having a
conditional formula that subtracts 12 hours only if the value is > 0.5 (noon).

However, your method would actually give wrong results, as times for the
hours beginning at noon and midnight would display as 00:00 to 00:59, rather
than the correct 12:00 to 12:59.

And as you can see from the link, we are not talking about a handful of
times, but perhaps 1000 per timetable/worksheet. This adds considerably to
the bulk, and adds complexity. Sure, it's possible to do (by adding one
hidden column per visible column), but numeric formatting would be so much
easier that I figured that I might as well suggest that an extra option be
added to the numeric formatting capabilities.

 

[Roger Govier]

Hi Philip

What a confusing timetable. I'm glad I don't live in Melbourne<bg>
Clearly columns labelled AM or PM actually have no relevance, as they
contain times which can be either.
I think I'll stick to a good old 24 hour clock, as with most travel
systems I've seen, then I know where I am.