Summing decimal values to time

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sunflower - 22 Apr 2007 22:53 GMT

I have a column of decimal values, A1 = 6, A2 = 1.30, A3 = .30 How do
I get the Total to sum with result of 8 hours and not 7.60?

All have is much appreciated

Reply to this Message

Sandy Mann - 23 Apr 2007 00:20 GMT

You would make your life a whole lot easier for yourself if you used XL
times like 1:30 instead of decimal numbers. You can then just SUM() the
times up as normal and it they are liable to sum to more then 24 hours,
custom format the SUM() cell as [h]:mm

if you already have the numbers entered and want to add them up as a one off
then use:

=INT(SUMPRODUCT(INT(A1:A200)+MOD(A1:A200,1)/0.6))+MOD(SUMPRODUCT(INT(A1:A200)+MOD(A1:A200,1)/0.6),1)*0.6

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 have a column of decimal values, A1 = 6, A2 = 1.30, A3 = .30 How do
> I get the Total to sum with result of 8 hours and not 7.60?
>
> All have is much appreciated

Reply to this Message

Sunflower - 24 Apr 2007 02:20 GMT

That worked perfectly! Thanks so much!

> You would make your life a whole lot easier for yourself if you used XL
> times like 1:30 instead of decimal numbers. You can then just SUM() the
[quoted text clipped - 22 lines]
>
> - Show quoted text -

Reply to this Message

Sandy Mann - 24 Apr 2007 09:39 GMT

You are very welcome. I would, however, still strongly recommend that you
convert to using real XL times, it will avoid many many problems. If you do
use XL times and you want to convert the times to decimal hours, (to
calculuate wages etc.), then as Teethless Mama did, multiply the time by 24
and you will get a number that can be used to multiply by the wage rate.

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

That worked perfectly! Thanks so much!

On Apr 22, 4:20 pm, "Sandy Mann" <sandyma...@mailinator.com> wrote:

> You would make your life a whole lot easier for yourself if you used XL
> times like 1:30 instead of decimal numbers. You can then just SUM() the
[quoted text clipped - 27 lines]
>
> - Show quoted text -

Reply to this Message

Sunflower - 25 Apr 2007 02:25 GMT

I will convert as you suggested :)
Thanks for all your help

> You are very welcome. I would, however, still strongly recommend that you
> convert to using real XL times, it will avoid many many problems. If you do
[quoted text clipped - 49 lines]
>
> - Show quoted text -

Reply to this Message

Teethless mama - 23 Apr 2007 04:20 GMT

Try this:

=TIME(INT(SUM(A1:A3)),MOD(SUM(A1:A3),1)*100,0)*24

Format cells as General

> I have a column of decimal values, A1 = 6, A2 = 1.30, A3 = .30 How do
> I get the Total to sum with result of 8 hours and not 7.60?
>
> All have is much appreciated

Reply to this Message

T. Valko - 23 Apr 2007 07:08 GMT

Try that using these values:

1.59 = 1:59
0.30 = 0:30
3.35 = 3:35

Biff

> Try this:
>
[quoted text clipped - 6 lines]
>>
>> All have is much appreciated

Reply to this Message

Sunflower - 24 Apr 2007 02:25 GMT

That worked as well, thank you!

On Apr 22, 8:20 pm, Teethless mama
<Teethlessm...@discussions.microsoft.com> wrote:

> Try this:
>
[quoted text clipped - 8 lines]
>
> - Show quoted text -

Reply to this Message

Rate this thread: