Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculations based on PivotTable information | Excel Discussion (Misc queries) | |||
looping through a set of calculations | Excel Worksheet Functions | |||
Calculations crossing multiple sheets | Excel Discussion (Misc queries) | |||
time interval calculations in excel | Excel Discussion (Misc queries) | |||
timesheet with running total of overtime | Excel Worksheet Functions |