Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Dec 2000
    Location
    Temple, Texas, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Format midnight as 24:00 (Excel 2000 SR-1)

    I am trying to figure out if there's a way to have Excel format midnight as 24:00 instead of 00:00. (It's a long story why - I know doing it that way seems counter-intuitive.) For example, if I enter 7/1/03 24:00, Excel converts it to 7/2/03 00:00. The folks I am preparing the work sheet for want it formatted as 7/1/03 24:00. I searched the Knowledge Base but didn't find anything. Thanks for any ideas.
    Jim Whitt
    Pharmacist
    Temple, Texas

  2. #2
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format midnight as 24:00 (Excel 2000 SR-1)

    Jim,

    Try this link. Format cell C9 with the appropriate date format and enter in the your date/time.

    John

  3. #3
    Star Lounger
    Join Date
    Dec 2000
    Location
    Temple, Texas, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format midnight as 24:00 (Excel 2000 SR-1)

    I left out a vital piece of information. I'm doing calculations with this - starting at x date and time and adding y number of hours to it. Sometimes the value of y is greater than 24 hours, so I need the date as well as the time. Sorry I left that out.
    Jim Whitt
    Pharmacist
    Temple, Texas

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Format midnight as 24:00 (Excel 2000 SR-1)

    I do NOT think there is any way to do this and keep it as a date and time number. The only way I can see doing it is to have some routine which converts it into a STRING which looks like a date and time. You could keep 2 cells:
    One for "real date/time" (hidden if desired) for calculations and another "display time" with a function that converts the serial date time to a text string where 24:00 is "Acceptable".

    But having 24:00 as a time is like have a time of 1:60 (which excel will "correctly" convert to 2:00). TIme goes from 0:00 to 23:59:59 then it is a new day. ELAPSED hours can go over 24 (format "[h]:mm") but it is NOT a TIME. It is a total hours.

    Steve

  5. #5
    Star Lounger
    Join Date
    Dec 2000
    Location
    Temple, Texas, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format midnight as 24:00 (Excel 2000 SR-1)

    Thanks for the responses. This pretty much confirms what I thought originally. Thanks again.
    Jim Whitt
    Pharmacist
    Temple, Texas

Posting Permissions

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