Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel hh:mm format
In some worksheets, a cell formatted hh:mm with a value of 24 will yield the
desired result - 24:00 (24 hours). As of today some worksheets are yielding 00:00 for the same value of 24, and in the formula bar, you can see that the 24 has been converted to 1/24/1900 12:00:00AM - the 24th day of the century. How can this be corrected so that it will show 24 hours. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel hh:mm format
To get 24:00 displayed, you need a format of [hh]:mm, not hh:mm.
However, I doubt that's your problem. To get the results you are showing, someone must have entered 24, as opposed to 24:00, which Excel interpreted as 24 days, rather than 24 hours. Correct the data entry, and you should be fine. Regards, Fred. "The Mysterious J" wrote in message ... In some worksheets, a cell formatted hh:mm with a value of 24 will yield the desired result - 24:00 (24 hours). As of today some worksheets are yielding 00:00 for the same value of 24, and in the formula bar, you can see that the 24 has been converted to 1/24/1900 12:00:00AM - the 24th day of the century. How can this be corrected so that it will show 24 hours. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel hh:mm format
This still didn't quite work. Using format [hh]:mm and entering the value 24
still converted the 24 into 01/24/1900 12:00 a.m., only now the display shows 576:00 - because 24 days into the century meant 576 hours had passed. Is there something else I should be changing to get the value 24 to show as 24 hours, and not 24 days? "Fred Smith" wrote: To get 24:00 displayed, you need a format of [hh]:mm, not hh:mm. However, I doubt that's your problem. To get the results you are showing, someone must have entered 24, as opposed to 24:00, which Excel interpreted as 24 days, rather than 24 hours. Correct the data entry, and you should be fine. Regards, Fred. "The Mysterious J" wrote in message ... In some worksheets, a cell formatted hh:mm with a value of 24 will yield the desired result - 24:00 (24 hours). As of today some worksheets are yielding 00:00 for the same value of 24, and in the formula bar, you can see that the 24 has been converted to 1/24/1900 12:00:00AM - the 24th day of the century. How can this be corrected so that it will show 24 hours. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel hh:mm format
J --
I think the previous answer may have addressed the problem -- if you're entering '24', that's the issue. You should be entering '24:00'. Does that fix it? HTH "The Mysterious J" wrote: This still didn't quite work. Using format [hh]:mm and entering the value 24 still converted the 24 into 01/24/1900 12:00 a.m., only now the display shows 576:00 - because 24 days into the century meant 576 hours had passed. Is there something else I should be changing to get the value 24 to show as 24 hours, and not 24 days? "Fred Smith" wrote: To get 24:00 displayed, you need a format of [hh]:mm, not hh:mm. However, I doubt that's your problem. To get the results you are showing, someone must have entered 24, as opposed to 24:00, which Excel interpreted as 24 days, rather than 24 hours. Correct the data entry, and you should be fine. Regards, Fred. "The Mysterious J" wrote in message ... In some worksheets, a cell formatted hh:mm with a value of 24 will yield the desired result - 24:00 (24 hours). As of today some worksheets are yielding 00:00 for the same value of 24, and in the formula bar, you can see that the 24 has been converted to 1/24/1900 12:00:00AM - the 24th day of the century. How can this be corrected so that it will show 24 hours. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel hh:mm format
No. As you found out, Excel interprets 24 as 24 days. For Excel to recognize
an entry as a time, you must include a colon. Your choices a 1. Enter the colon. 2. Divide the entry by 24 (the number of hours in a day) 3. Write a macro which converts 24 days into 24 hours. Regards, Fred. "The Mysterious J" wrote in message ... This still didn't quite work. Using format [hh]:mm and entering the value 24 still converted the 24 into 01/24/1900 12:00 a.m., only now the display shows 576:00 - because 24 days into the century meant 576 hours had passed. Is there something else I should be changing to get the value 24 to show as 24 hours, and not 24 days? "Fred Smith" wrote: To get 24:00 displayed, you need a format of [hh]:mm, not hh:mm. However, I doubt that's your problem. To get the results you are showing, someone must have entered 24, as opposed to 24:00, which Excel interpreted as 24 days, rather than 24 hours. Correct the data entry, and you should be fine. Regards, Fred. "The Mysterious J" wrote in message ... In some worksheets, a cell formatted hh:mm with a value of 24 will yield the desired result - 24:00 (24 hours). As of today some worksheets are yielding 00:00 for the same value of 24, and in the formula bar, you can see that the 24 has been converted to 1/24/1900 12:00:00AM - the 24th day of the century. How can this be corrected so that it will show 24 hours. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel hh:mm format
Perfect! Thank you. It also makes sense why we just ran into this problem now
- most of our services are in minutes (0:60, 0:15, 0:45, 1:30, et cetera). This only came up because it was for inpatient services, which are 24-hour services. I'll pass it on to my team. "Fred Smith" wrote: No. As you found out, Excel interprets 24 as 24 days. For Excel to recognize an entry as a time, you must include a colon. Your choices a 1. Enter the colon. 2. Divide the entry by 24 (the number of hours in a day) 3. Write a macro which converts 24 days into 24 hours. Regards, Fred. "The Mysterious J" wrote in message ... This still didn't quite work. Using format [hh]:mm and entering the value 24 still converted the 24 into 01/24/1900 12:00 a.m., only now the display shows 576:00 - because 24 days into the century meant 576 hours had passed. Is there something else I should be changing to get the value 24 to show as 24 hours, and not 24 days? "Fred Smith" wrote: To get 24:00 displayed, you need a format of [hh]:mm, not hh:mm. However, I doubt that's your problem. To get the results you are showing, someone must have entered 24, as opposed to 24:00, which Excel interpreted as 24 days, rather than 24 hours. Correct the data entry, and you should be fine. Regards, Fred. "The Mysterious J" wrote in message ... In some worksheets, a cell formatted hh:mm with a value of 24 will yield the desired result - 24:00 (24 hours). As of today some worksheets are yielding 00:00 for the same value of 24, and in the formula bar, you can see that the 24 has been converted to 1/24/1900 12:00:00AM - the 24th day of the century. How can this be corrected so that it will show 24 hours. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to format cells in Excel 2007 (i.e. currency format)? | Excel Discussion (Misc queries) | |||
Convert Excel 2007 format (*.xlsx) into Excel 2003 format | Excel Discussion (Misc queries) | |||
Excel Auto-Format cell to email format | Excel Discussion (Misc queries) | |||
How to covert picture excel format to editable excel format | Excel Discussion (Misc queries) | |||
how to format excel format to text format with separator "|" in s. | New Users to Excel |