+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Philip J. Rayment
    Guest

    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

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    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

  3. #3
    Philip J. Rayment
    Guest

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

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


  4. #4
    Roger Govier
    Guest

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

    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" <PhilipJRayment@discussions.microsoft.com> wrote in
    message news:8133F089-EBE4-4FA4-A4E3-C7A4E2E15A4D@microsoft.com...
    > "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.
    >




  5. #5
    Philip J. Rayment
    Guest

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

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

  6. #6
    Roger Govier
    Guest

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

    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" <PhilipJRayment@discussions.microsoft.com> wrote in
    message news:F385D396-8F10-4E43-B302-FCA8058C0174@microsoft.com...
    > "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.




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1