Timesheet Calculations
 

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!

Worker Time In Lunch Time Out Total Billed Diff
Bob 12:30 0:00 14:30 2:00 2.00 0.00
Jeff 12:30 0:00 14:30 2:00 2.00 0.00
Bob 10:00 0.5 17:00 6.50 10.00 3.50
Jeff 10:00 0.5 17:00 6.50 10.00 3.50
Bob 10:30 0.5 17:00 6.00 8.00 2.00
Jeff 10:30 0.5 17:00 6.00 8.00 2.00

Timesheet Calculations
 

hard 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 + (B2D2)) - 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
http://www.mvps.org/dmcritchie/excel/datetime.htm
http://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: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"CS Project Man" wrote in message
...
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!

Worker Time In Lunch Time Out Total Billed Diff
Bob 12:30 0:00 14:30 2:00 2.00 0.00
Jeff 12:30 0:00 14:30 2:00 2.00 0.00
Bob 10:00 0.5 17:00 6.50 10.00 3.50
Jeff 10:00 0.5 17:00 6.50 10.00 3.50
Bob 10:30 0.5 17:00 6.00 8.00 2.00
Jeff 10:30 0.5 17:00 6.00 8.00 2.00

Timesheet Calculations
 

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!

"David McRitchie" wrote:

hard 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 + (B2D2)) - 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
http://www.mvps.org/dmcritchie/excel/datetime.htm
http://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: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"CS Project Man" wrote in message
...
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!

Worker Time In Lunch Time Out Total Billed Diff
Bob 12:30 0:00 14:30 2:00 2.00 0.00
Jeff 12:30 0:00 14:30 2:00 2.00 0.00
Bob 10:00 0.5 17:00 6.50 10.00 3.50
Jeff 10:00 0.5 17:00 6.50 10.00 3.50
Bob 10:30 0.5 17:00 6.00 8.00 2.00
Jeff 10:30 0.5 17:00 6.00 8.00 2.00

Timesheet Calculations
 

If lunch break is in decimal, formula is what I supplied
E2: = 24 * (D2 - B2 + (B2D2)) - 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 + (B2D2) - C2 ) format as decimal number 0.00