adding minutes and hours

P

papa

Hi,

My apologies if this has already been posted, but I am
looking for a quick and easy way to add minutes and hours
together

ie. I have a row in excel for overtime,
the user enters
1.20 , .40, 2.10, 1.10, .35

these five entries will be the overtinme for monday to
friday - is there any easy way to add them in hours and
minutes? (total for the week = 5.55)

Many thanks,
Papa
 
F

Frank Kabel

Hi
1. format the entry cells as 'Date' so that the user will enter '1:20',
'0:40', etc.
2. In the target cell just add them (e.g. =A1+B1+C1....)
3. Format the target cell with the format [h]:mm to enable more then 24
hours overtime :)

Frank
 
B

Bob Phillips

Hi Papa,

This formula seems to work

=SUM(INT(A1:A5))+INT(SUM((A1:A5-INT(A1:A5))*100)/60)+(SUM((A1:A5-INT(A1:A5))
*100)-INT(SUM((A1:A5-INT(A1:A5))*100)/60)*60)/100

but it is a bit awkward. I suggest that you enter your time as real time,
1:20, 0:40, etc), and then just add these up. Format the result as time as
well and it ius all easy.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
N

Norman Harker

Hi Papa!

With your times in A1:E1 the following seems to work OK:

=SUM(INT(A1:E1))+INT(SUM(MOD(A1:E1,1))*100/60)+MOD(SUM(MOD(A1:E1,1))*1
00/60,1)*(60/100)
Entered as an array by pressing and holding down Ctrl + Shift and then
pressing Enter.

Appears in the cell as:
{=SUM(INT(A1:E1))+INT(SUM(MOD(A1:E1,1))*100/60)+MOD(SUM(MOD(A1:E1,1))*
100/60,1)*(60/100)}

But hang around as these challenges usually result in quite a few
alternatives.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
N

Norman Harker

Hi Papa!

Bob's formula has to be entered as an array by pressing and holding
down Ctrl + Shift and then hitting the Enter key.

I'll definitely second the advice to get times entered as 1:30, 0:45
etc as =SUM(F4:J4) is a tad easier as a formula.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
B

Bob Phillips

Thanks Norm, forgot that bit.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top