Summing decimal values to time
 

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

Summing decimal values to time
 

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


with @tiscali.co.uk


"Sunflower" wrote in message
ups.com...
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

Summing decimal values to time
 

Try this:

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

Format cells as General


"Sunflower" wrote:

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

Summing decimal values to time
 

Try that using these values:

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

Biff

"Teethless mama" wrote in message
...
Try this:

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

Format cells as General


"Sunflower" wrote:

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

Summing decimal values to time
 

That worked perfectly! Thanks so much!

On Apr 22, 4:20 pm, "Sandy Mann" 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
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


with @tiscali.co.uk

"Sunflower" wrote in message

ups.com...



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- Hide quoted text -

- Show quoted text -

Summing decimal values to time
 

That worked as well, thank you!

On Apr 22, 8:20 pm, Teethless mama
wrote:
Try this:

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

Format cells as General



"Sunflower" wrote:
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- Hide quoted text -

- Show quoted text -

Summing decimal values to time
 

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


with @tiscali.co.uk


"Sunflower" wrote in message
oups.com...
That worked perfectly! Thanks so much!

On Apr 22, 4:20 pm, "Sandy Mann" 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
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


with @tiscali.co.uk

"Sunflower" wrote in message

ups.com...



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- Hide quoted text -

- Show quoted text -

Summing decimal values to time
 

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

On Apr 24, 1:39 am, "Sandy Mann" wrote:
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


with @tiscali.co.uk

"Sunflower" wrote in message

oups.com...
That worked perfectly! Thanks so much!

On Apr 22, 4:20 pm, "Sandy Mann" 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
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


with @tiscali.co.uk

"Sunflower" wrote in message

oups.com...

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -