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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
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

(e-mail address removed)
(e-mail address removed) 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- Hide quoted text -

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

(e-mail address removed)
(e-mail address removed) 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

(e-mail address removed)
(e-mail address removed) 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- Hide quoted text -

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

(e-mail address removed)
(e-mail address removed) 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:

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
- Show quoted text -- Hide quoted text -

- Show quoted text -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top