Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hours and minutes display as date in formula box
When I enter hours and minutes in a cell (example: 93:11:00) the formula box
shows 1/3/1900 9:11:00pm. How do you get the formula box to display 93:11:00? Also, is there a way to display only hours and minutes (93:11) since I don't work with seconds? Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hours and minutes display as date in formula box
Hi Dave,
One way is to apply the custom format h:mm to the cell(s) and type an apostrophe (') before the value eg '93:11. The apostrophe appears in the formula bar but not in the cell, and you will be able to do calculations this way too. To apply the format go FormatCellsNumberCustom then type h:mm in the Type: box. Ken Johnson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hours and minutes display as date in formula box
try custon format of [h]mm
-- paul remove nospam for email addy! "Dave" wrote: When I enter hours and minutes in a cell (example: 93:11:00) the formula box shows 1/3/1900 9:11:00pm. How do you get the formula box to display 93:11:00? Also, is there a way to display only hours and minutes (93:11) since I don't work with seconds? Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hours and minutes display as date in formula box
On Thu, 5 Jan 2006 17:36:02 -0800, "Dave"
wrote: When I enter hours and minutes in a cell (example: 93:11:00) the formula box shows 1/3/1900 9:11:00pm. How do you get the formula box to display 93:11:00? As far as I know, there is no way to control what is displayed in the formula bar. Also, is there a way to display only hours and minutes (93:11) since I don't work with seconds? Thanks. Select the cell; then Format/Cells/Number/Custom Type: [h]:mm --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hours and minutes display as date in formula box
On 5 Jan 2006 18:03:08 -0800, "Ken Johnson" wrote:
Hi Dave, One way is to apply the custom format h:mm to the cell(s) and type an apostrophe (') before the value eg '93:11. The apostrophe appears in the formula bar but not in the cell, and you will be able to do calculations this way too. To apply the format go FormatCellsNumberCustom then type h:mm in the Type: box. Ken Johnson That is misleading. Although operators will usually interpret an entry of the form '93:11 correctly, commonly used worksheet functions will not do so. Try using the SUM function on that cell -- it evaluates to zero. --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hours and minutes display as date in formula box
Hi Ron,
Thanks for that. I just tried A2 - A1, which worked, so I assumed everything OK with calculation. A classic case of "when I assume I put an ass before u and me"! I'll try to be more careful in future, but with so much to learn about Excel, I can't make any promises. Ken Johnson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hours and minutes display as date in formula box
Hi Ron,
I meant to ask... What is the difference between [h]:mm and h:mm? Ken Johnson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hours and minutes display as date in formula box
Hi Ron,
This might appear twice, something seemed to go wrong first time. Anyhow, thanks Ron, I just tried A2-A1 and it worked so I assumed all calculations would be fine. Looks like a classic case of "When one assumes one puts an ass before u and me". I'll try to be more careful in future, but with so much to learn about Excel I can't make any promises. Ken Johnson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hours and minutes display as date in formula box
On 6 Jan 2006 04:26:30 -0800, "Ken Johnson" wrote:
Hi Ron, I meant to ask... What is the difference between [h]:mm and h:mm? Ken Johnson Without the brackets around the h, the h will show h mod 24, so 93:11 would show as 21:11 --ron |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hours and minutes display as date in formula box
"Ron Rosenfeld" wrote: On 6 Jan 2006 04:26:30 -0800, "Ken Johnson" wrote: Hi Ron, I meant to ask... What is the difference between [h]:mm and h:mm? Ken Johnson Without the brackets around the h, the h will show h mod 24, so 93:11 would show as 21:11 --ron It works. Thanks to all of you. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hours and minutes display as date in formula box
Thanks Ron.
Ken Johnson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hours and minutes display as date in formula box
Hi Ron,
I notice that worksheet functions only work if the cells with the ' prefix are entered inside the VALUE function. Thanks for pointing that out. Ken Johnson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hours and minutes display as date in formula box
Hi Ron,
Try using the SUM function on that cell -- it evaluates to zero. Thanks for pointing out my mistake. Everything seemed fine when I tried A2 - A1 so I unwittingly assumed all calculations would be fine. I now see that worksheet functions will only work with these cells, with the ' prefix, if they are entered as their value eg SUM(VALUE(A1),VALUE(A2)). Ken Johnson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hours and minutes display as date in formula box
You want the Time Category in Format Cells:Number. You can select the format
37:30:55, which will display 93:11:00, then choose Custom and delete the seconds. The resulting customized category will look like [h]:mm and will show hours and minutes but not seconds. "Dave" wrote: When I enter hours and minutes in a cell (example: 93:11:00) the formula box shows 1/3/1900 9:11:00pm. How do you get the formula box to display 93:11:00? Also, is there a way to display only hours and minutes (93:11) since I don't work with seconds? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding hours and minutes | Excel Worksheet Functions | |||
How to convert minutes to fractional hours | Excel Worksheet Functions | |||
adding rows of hours and minutes to get a total | Excel Worksheet Functions | |||
Help for a newbie trying to add hours and minutes :) | Excel Discussion (Misc queries) | |||
convert interval to various separate date , time, hr, minutes | Excel Worksheet Functions |