 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 2008Excel 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. 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. > In some worksheets, a cell formatted hh:mm with a value of 24 will yield > the [quoted text clipped - 5 lines] > century. > How can this be corrected so that it will show 24 hours. 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? > To get 24:00 displayed, you need a format of [hh]:mm, not hh:mm. > [quoted text clipped - 15 lines] > > century. > > How can this be corrected so that it will show 24 hours. 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 > 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 [quoted text clipped - 21 lines] > > > century. > > > How can this be corrected so that it will show 24 hours. 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 are: 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. > This still didn't quite work. Using format [hh]:mm and entering the value > 24 [quoted text clipped - 28 lines] >> > century. >> > How can this be corrected so that it will show 24 hours. 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. > 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 are: [quoted text clipped - 38 lines] > >> > century. > >> > How can this be corrected so that it will show 24 hours. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.