Summing decimal values to time

S

Sunflower

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
 
S

Sandy Mann

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

--
HTH

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

[email protected]
[email protected] with @tiscali.co.uk
 
T

Teethless mama

Try this:

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

Format cells as General
 
S

Sunflower

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
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:A20­0)+MOD(A1:A200,1)/0.6),1)*0.6

--
HTH

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

[email protected]
[email protected] 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?
Click to expand...
All have is much appreciated- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
S

Sandy Mann

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.

--
HTH

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

[email protected]
[email protected] with @tiscali.co.uk


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
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:A20­0)+MOD(A1:A200,1)/0.6),1)*0.6

--
HTH

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

[email protected]
[email protected] 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?
Click to expand...
All have is much appreciated- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
S

Sunflower

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 youdo
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.

--
HTH

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

[email protected]
[email protected] with @tiscali.co.uk


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
times up as normal and it they are liable to sum to more then 24 hours,
custom format the SUM() cell as [h]:mm
Click to expand...
if you already have the numbers entered and want to add them up as a one
off
then use:

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
Click to expand...
[email protected]
[email protected] with @tiscali.co.uk
Click to expand...
- Show quoted text -- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
Top