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

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

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

4. ## 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. ## Re: Format midnight as 24:00 (Excel 2000 SR-1)

Thanks for the responses. This pretty much confirms what I thought originally. Thanks again.

#### Posting Permissions

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