"Roger Govier" wrote:

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/tr...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.