software

I have some freelancers that have submitted timesheets that don't match the
hours I have them worked. I can do it manually but is there a formula for the
following?

Time In minus Lunch, that total with Time Out equals Total time worked. Then
the difference of Total time worked from Billed. Thanks!

WorkerTime InLunchTime OutTotalBilledDiff
Bob12:300:0014:302:002.000.00
Jeff12:300:0014:302:002.000.00
Bob10:000.517:006.5010.003.50
Jeff10:000.517:006.5010.003.50
Bob10:300.517:006.008.002.00
Jeff10:300.517:006.008.002.00hard to read example,
will assume that lunch is decimal number
seems this question was asked over and over without the data
You cannot code column C interchangeably as time or as decimal
unless the number is zero.

A2: Bob (Worker)
B2: 12:30 (Time In)
C2 0 (Lunch hours in decimal -- most of the others are in decimal)
D2: 14:30 (Time out)
E2: 2:00 (Total hours in decimal)
F2: 2:00 (Billed hours in decimal))
G2: 0.00 (Diff hours in decimal)

E2: = 24 * (D2 - B2 (B2gt;D2)) - C2 format as decimal number 0.00

Time is measured as a fraction of day, so must be mulitiplied by 24 to get hours.

More information on Date and Time
www.mvps.org/dmcritchie/excel/datetime.htm
www.cpearson.com/excel/datetime.htm
These should greatly reduce any need to ask questions on Date and Time.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
Search Page: www.mvps.org/dmcritchie/excel/search.htm

quot;CS Project Manquot; gt; wrote in message
...
gt; I have some freelancers that have submitted timesheets that don't match the
gt; hours I have them worked. I can do it manually but is there a formula for the
gt; following?
gt;
gt; Time In minus Lunch, that total with Time Out equals Total time worked. Then
gt; the difference of Total time worked from Billed. Thanks!
gt;
gt; Worker Time In Lunch Time Out Total Billed Diff
gt; Bob 12:30 0:00 14:30 2:00 2.00 0.00
gt; Jeff 12:30 0:00 14:30 2:00 2.00 0.00
gt; Bob 10:00 0.5 17:00 6.50 10.00 3.50
gt; Jeff 10:00 0.5 17:00 6.50 10.00 3.50
gt; Bob 10:30 0.5 17:00 6.00 8.00 2.00
gt; Jeff 10:30 0.5 17:00 6.00 8.00 2.00
gt;
Sorry it's so hard to read, I cut and paste from Excel. What you posted works
and I could have gone with minutes rather that decimal for the lunch period.
Though the decimal made it easier to calculate the difference that I was
charged. Thanks for the help!

quot;David McRitchiequot; wrote:

gt; hard to read example,
gt; will assume that lunch is decimal number
gt; seems this question was asked over and over without the data
gt; You cannot code column C interchangeably as time or as decimal
gt; unless the number is zero.
gt;
gt; A2: Bob (Worker)
gt; B2: 12:30 (Time In)
gt; C2 0 (Lunch hours in decimal -- most of the others are in decimal)
gt; D2: 14:30 (Time out)
gt; E2: 2:00 (Total hours in decimal)
gt; F2: 2:00 (Billed hours in decimal))
gt; G2: 0.00 (Diff hours in decimal)
gt;
gt; E2: = 24 * (D2 - B2 (B2gt;D2)) - C2 format as decimal number 0.00
gt;
gt; Time is measured as a fraction of day, so must be mulitiplied by 24 to get hours.
gt;
gt; More information on Date and Time
gt; www.mvps.org/dmcritchie/excel/datetime.htm
gt; www.cpearson.com/excel/datetime.htm
gt; These should greatly reduce any need to ask questions on Date and Time.
gt;
gt; HTH,
gt; David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
gt; My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
gt; Search Page: www.mvps.org/dmcritchie/excel/search.htm
gt;
gt; quot;CS Project Manquot; gt; wrote in message
gt; ...
gt; gt; I have some freelancers that have submitted timesheets that don't match the
gt; gt; hours I have them worked. I can do it manually but is there a formula for the
gt; gt; following?
gt; gt;
gt; gt; Time In minus Lunch, that total with Time Out equals Total time worked. Then
gt; gt; the difference of Total time worked from Billed. Thanks!
gt; gt;
gt; gt; Worker Time In Lunch Time Out Total Billed Diff
gt; gt; Bob 12:30 0:00 14:30 2:00 2.00 0.00
gt; gt; Jeff 12:30 0:00 14:30 2:00 2.00 0.00
gt; gt; Bob 10:00 0.5 17:00 6.50 10.00 3.50
gt; gt; Jeff 10:00 0.5 17:00 6.50 10.00 3.50
gt; gt; Bob 10:30 0.5 17:00 6.00 8.00 2.00
gt; gt; Jeff 10:30 0.5 17:00 6.00 8.00 2.00
gt; gt;
gt;
gt;
gt;

If lunch break is in decimal, formula is what I supplied
E2: = 24 * (D2 - B2 (B2gt;D2)) - C2 format as decimal number 0.00
no problem with entering decimal time for break period, if in increments of .25 (15 minutes)

If lunch break is as h:mm, formula would be
E2: = 24 * (D2 - B2 (B2gt;D2) - C2 ) format as decimal number 0.00