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

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/comm...lic.excel.misc

This is a good case for why macro code exists. Here a simple UDF would solve the problem. One that many members of the various news and support groups could easily code. 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.

Leith Ross

"Leith Ross" wrote:

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

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)

--
Regards

Roger Govier


"Philip J. Rayment" <[email protected]> wrote in
message news:[email protected]...
> "Leith Ross" wrote:
>
>> 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" 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.

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" <[email protected]> wrote in
message news:[email protected]...
> "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.