Calculating Amount Due from Hours Worked

C

Chris Benham

Okay, I am working on a time-card/invoice sheet that I can use to clock
in and out and compute the amount of time worked for that shift. I
already have this portion figured out, using the formula
=TEXT(C2-B2,"h:mm"). I end up with a h:mm result (i.e. 1:20, meaning
one hour and twenty minutes worked).

Now I need to add all of these results together to get a total number of
hours and minutes worked, and then multiply that amount by my hourly
rate to get a total amount due.

Any advice on how to accomplish this?

Chris
 
C

Chip Pearson

Chris,

First of all, don't use the TEXT function in this case, because
it returns a text string rather than a numerical value that can
be used in calculations. Simply use the formula =C2-B2 and give
a number format of hh:mm to the cell (Format menu, Cells, then
the Number tab). Then, you can add up all the times using the SUM
function; e.g., =SUM(D2:D10). For this cell, use a custom number
format of [hh]:mm . The square brackets prevent Excel from
"rolling over" the displayed time value at 24:00. Finally,
multiply this cell by 24 then by the hourly rate to get the
amount due.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
A

Arvi Laanemets

Hi

No need for converting the substract as string - you can do with time format
nicely.

=C2-B2
and format the cell with formula in it as Custom "hh:mm".

In case the working time can end after midnight, use the formula
=C2-B2+(C2<B2)
instead.

To add times, use ordinary SUM function, like
=SUM(D2:D19)
and format the cell as Custom "[h]:mm".

To get total amount, with summary time in p.e. D20
=D20*24*HourlyRate
and format the cell as currency (or number or general).
 
C

Chris Benham

Thanks a ton! That got it for me.

I can't believe that earlier today I was computing complicated
statistical formulae and CHI-Square distributions, and this little beast
had me stumped.

Chris
 
Top