Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Limiting a cell to an upper value (and passing on the difference)
I'm in the process of making a timesheet.
in column A1 will be the start time (00:00); in B1 will be the finish time (00:00). I want to calculate the hours between these two times (hrs) but not allow the figure in C1 to go above 8hrs. Any additional hours (over 8) will be moved to D1. Example: A1 (08:00) B1 (20:00) C1 (8hrs) D1 (4hrs) Column A = start work Column B = finish work Column C = hours at normal rate (up to 8 hours) Column D = hours at 1/2 time rate (number of hours over 8) Any help you could give would be much appreciated |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Limiting a cell to an upper value (and passing on the difference)
For column C I would enter =IF((B-A)*248,8*hourly rate,(B-A)*24*hourly rate)
For column D I would enter =IF((B-A)*248,(((B-A)*24)-(value in C))*1.5(hourly rate)),0) B-A needs to be multiplied by 24 because Excel interprets time as fractions of 24; i.e, 20:00 = 8:00PM = 20/24 = 5/6 = .83333, etc. Column C's logic is "If the amount of hours worked is greater than 8, THEN multiply 8 by the hourly rate, ELSE multiply the number of hours times the hourly rate." Column D's logic is "If the amount of hours worked is greater than 8, THEN subtract 8 from total hours worked and multiply that by 1.5 times the hourly rate, ELSE 0." Dave -- Brevity is the soul of wit. "Dri" wrote: I'm in the process of making a timesheet. in column A1 will be the start time (00:00); in B1 will be the finish time (00:00). I want to calculate the hours between these two times (hrs) but not allow the figure in C1 to go above 8hrs. Any additional hours (over 8) will be moved to D1. Example: A1 (08:00) B1 (20:00) C1 (8hrs) D1 (4hrs) Column A = start work Column B = finish work Column C = hours at normal rate (up to 8 hours) Column D = hours at 1/2 time rate (number of hours over 8) Any help you could give would be much appreciated |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Limiting a cell to an upper value (and passing on the differen
Thanks Dave for your reply
I'm not actually bothered in this equation with how much a wage would be - only in the number of hours and minutes which fall within column C or D "Dave F" wrote: For column C I would enter =IF((B-A)*248,8*hourly rate,(B-A)*24*hourly rate) For column D I would enter =IF((B-A)*248,(((B-A)*24)-(value in C))*1.5(hourly rate)),0) B-A needs to be multiplied by 24 because Excel interprets time as fractions of 24; i.e, 20:00 = 8:00PM = 20/24 = 5/6 = .83333, etc. Column C's logic is "If the amount of hours worked is greater than 8, THEN multiply 8 by the hourly rate, ELSE multiply the number of hours times the hourly rate." Column D's logic is "If the amount of hours worked is greater than 8, THEN subtract 8 from total hours worked and multiply that by 1.5 times the hourly rate, ELSE 0." Dave -- Brevity is the soul of wit. "Dri" wrote: I'm in the process of making a timesheet. in column A1 will be the start time (00:00); in B1 will be the finish time (00:00). I want to calculate the hours between these two times (hrs) but not allow the figure in C1 to go above 8hrs. Any additional hours (over 8) will be moved to D1. Example: A1 (08:00) B1 (20:00) C1 (8hrs) D1 (4hrs) Column A = start work Column B = finish work Column C = hours at normal rate (up to 8 hours) Column D = hours at 1/2 time rate (number of hours over 8) Any help you could give would be much appreciated |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Limiting a cell to an upper value (and passing on the difference)
Perhaps Chip Pearson can help you with your timesheet.
See his examples at http://www.cpearson.com/excel/overtime.htm Gord Dibben MS Excel MVP On Fri, 8 Sep 2006 07:49:02 -0700, Dri wrote: I'm in the process of making a timesheet. in column A1 will be the start time (00:00); in B1 will be the finish time (00:00). I want to calculate the hours between these two times (hrs) but not allow the figure in C1 to go above 8hrs. Any additional hours (over 8) will be moved to D1. Example: A1 (08:00) B1 (20:00) C1 (8hrs) D1 (4hrs) Column A = start work Column B = finish work Column C = hours at normal rate (up to 8 hours) Column D = hours at 1/2 time rate (number of hours over 8) Any help you could give would be much appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|