adding minutes and hours

  • Thread starter Thread starter papa
  • Start date Start date
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
 
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
 
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)
 
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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
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)
 
Back
Top