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.

--
Regards

Roger Govier


"Philip J. Rayment" wrote in
message ...
"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.