Prev: Text to Columns
Next: Stacked Column Charts help!
From: Tacrier on 10 Feb 2010 14:23
I have been reading the answers to hour/minute type questions but can't seem
to find the answer I need...

I am trying to calculate total hours worked in during a night shift like this:

A1 = 02/05/10 8:30 PM
B1 = 2/06/10 7:00 AM
C1 = B1-A1 = 10.30

I would like the 10.30 to appear as 10.5 instead.

Could anyone help me with this?

Trina C.
"Pete_UK" wrote:

> Use a custom format for the total cell:
>
> [h]:mm
>
> will stop hours above 25 being treated as days.
>
> The same can be accomplished by choosing the format "37:30:55" from the
> list provided under Format | Cells | Number (tab) | Time.
>
> Hope this helps.
>
> Pete
>
> Dave wrote:
> > Hi,
> > I need to add up a list of hour and minutes, the only problem being is that
> > Excel treats them as TIME not hours and minutes.
> >
> > For example, if I sum 7:24, 7:24 , 7:24, 7:24 [ where 7 = hours and 24 =
> > minutes ] then Excel five me an answer of 05:36 rather than 29.36 - any ideas
> > how I get this working ?
> >
> > Thanks
>
>
From: "David Biddulph" groups [at] on 10 Feb 2010 15:33
It won't be 10.30, it will be 10:30. There is a difference between a colon
and a decimal point.
If you want to turn it to decimal hours, multiply by 24 and format as
general or number instead of time.
--
David Biddulph

Tacrier wrote:
> I have been reading the answers to hour/minute type questions but
> can't seem to find the answer I need...
>
> I am trying to calculate total hours worked in during a night shift
> like this:
>
> A1 = 02/05/10 8:30 PM
> B1 = 2/06/10 7:00 AM
> C1 = B1-A1 = 10.30
>
> I would like the 10.30 to appear as 10.5 instead.
>
> Could anyone help me with this?
>
> Trina C.
> "Pete_UK" wrote:
>
>> Use a custom format for the total cell:
>>
>> [h]:mm
>>
>> will stop hours above 25 being treated as days.
>>
>> The same can be accomplished by choosing the format "37:30:55" from
>> the list provided under Format | Cells | Number (tab) | Time.
>>
>> Hope this helps.
>>
>> Pete
>>
>> Dave wrote:
>>> Hi,
>>> I need to add up a list of hour and minutes, the only problem being
>>> is that Excel treats them as TIME not hours and minutes.
>>>
>>> For example, if I sum 7:24, 7:24 , 7:24, 7:24 [ where 7 = hours and
>>> 24 = minutes ] then Excel five me an answer of 05:36 rather than
>>> 29.36 - any ideas how I get this working ?
>>>
>>> Thanks


 | 
Pages: 1
Prev: Text to Columns
Next: Stacked Column Charts help!