adding times

J

J.W. Aldridge

I have a timesheet and I need a formula that will ADD the following
times:

1.67
1.11

The natural result is:
2.78

But I need :

3.18

(Need the minutes to convert over. 60 minutes = 1 hour)
I dont to have to manually add everthing over .60


Any ideas?

Thanx
 
S

Sandy Mann

J.W.,

you are giving yourself a lot of trouble by trying to work with decimal
times. It is much better to use XL times like

1:40
1:07

(note that the hours and minutes are separated by a full colon)

You can then simply add the times like A1+A2 or SUM(A1:A2)


If your total is liable to exceed 24 hours then custom format the cell as
[h]:mm which will stop the hours turning into days when they exceed 24
hours.

--
HTH

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

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

T. Valko

To answer your question as posted:

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

I'm assuming entries like this:

1.05 = 1 min 5 sec
0.60 = 0 min 60 sec
0.05 = 0 min 5 sec

So:

1.60
1.60

= 4.00

0.05
0.55

= 1.00

Biff
 
Top