formula to turn hh:mm cell into x.y hours

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

FN - 13 May 2008 16:29 GMT

I need to track billing for a client, with 1/10th hour precision, and where
partial groups of 6 minutes round up to the next 1/10th hour (example: 8
minutes = 0.2 hours). I have a start time, and an end time. Then I
subtract one from the other and get hh:mm, such as 1:08 which represents 1
hour and 8 minutes. So far so good. But then I want a formula that
converts that to 1.2 hours. But everything I try is failing. I can't
even figure out how to convert the 1:08 to 68 minutes. Please help me with
the formula for the cell below:

Start Stop Duration BillableHours
8:00 9:08 1:08 ?????

????? should show 1.2

Thank you.

Reply to this Message

Beege - 13 May 2008 16:38 GMT

FN,

Try =C1*24, format as general or number.

Beege

> I need to track billing for a client, with 1/10th hour precision, and where
> partial groups of 6 minutes round up to the next 1/10th hour (example: 8
[quoted text clipped - 11 lines]
>
> Thank you.

Reply to this Message

Niek Otten - 13 May 2008 16:39 GMT

=ROUNDUP(C1*24,1)

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

|I need to track billing for a client, with 1/10th hour precision, and where
| partial groups of 6 minutes round up to the next 1/10th hour (example: 8
[quoted text clipped - 11 lines]
|
| Thank you.

Reply to this Message

Sandy Mann - 13 May 2008 16:42 GMT

=CEILING(C1*24,0.1)

and format as General or Number

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

>I need to track billing for a client, with 1/10th hour precision, and where
>partial groups of 6 minutes round up to the next 1/10th hour (example: 8
[quoted text clipped - 11 lines]
>
> Thank you.

Reply to this Message

FN - 14 May 2008 07:44 GMT

Thanks but what is the "24" all about? I don't understand the relationship
between "24" and wanting to convert hh:mm into hours DOT tenths.

> =CEILING(C1*24,0.1)
>
[quoted text clipped - 15 lines]
>>
>> Thank you.

Reply to this Message

Sandy Mann - 14 May 2008 08:43 GMT

Dates in XL are interger numbers being a count of days from 1/1/1900. Times
are fractions of days so 0.5 is 12 hours or mid-day, 0.25 is 9 AM etc. The
*times* are then formatted to *look* like times but the underlying value
held in the cell is still the fraction. There are 24 hours in a day so to
get from 12:00 (0.5) to 12 you have to multiply by 24. Thus 12:00 * 24 =
12. 13:30 formatted as General shows 0.5625 so 13:30 * 24 = 0.5625 etc.

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Thanks but what is the "24" all about? I don't understand the
> relationship between "24" and wanting to convert hh:mm into hours DOT
[quoted text clipped - 19 lines]
>>>
>>> Thank you.

Reply to this Message