Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate time if greater than a given time
Could one of you math wizards help me take a bite out of a math formula I am
attempting to formulate. I am trying it a lot of different ways sometimes formula errors sometimes not, but still no results in the cell. This has to do with payroll calculations. Here is what I have so far that works! cells [] [09:00] - [23:00] = [14.00] this works ie: =SUM((B10-A10)*24) This is what I need help on If I am working on the clock at 15:00 I get a shift differential for those hours beyond 15:00 hours until I clock out, so lets say from the above example. I would be payed for 14 hours at my regular rate then get a diff from 15:00 - 23:00 which is a difference of 8 hours I cannot seem to come up with a formula that will allow me in another cell to calculate the two time differences and anything greater than 15:00 place in that cell the total hours of 8 any help thanks Richard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate time if greater than a given time
Hi,
To get the result I think you want try this B10 C10 D10 E10 Start End Basic time Shift allowance 09:00 23:00 14 8 =(B10-A10)*24 In D10 =MAX(0,C10-((TIME(15,0,0)-A10)*24)) In E10 Mike "draculardw" wrote: Could one of you math wizards help me take a bite out of a math formula I am attempting to formulate. I am trying it a lot of different ways sometimes formula errors sometimes not, but still no results in the cell. This has to do with payroll calculations. Here is what I have so far that works! cells [] [09:00] - [23:00] = [14.00] this works ie: =SUM((B10-A10)*24) This is what I need help on If I am working on the clock at 15:00 I get a shift differential for those hours beyond 15:00 hours until I clock out, so lets say from the above example. I would be payed for 14 hours at my regular rate then get a diff from 15:00 - 23:00 which is a difference of 8 hours I cannot seem to come up with a formula that will allow me in another cell to calculate the two time differences and anything greater than 15:00 place in that cell the total hours of 8 any help thanks Richard |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate time if greater than a given time
That should of course have been
A10 B10 C10 D10 Start End Basic time Shift allowance 09:00 23:00 14 8 "Mike H" wrote: Hi, To get the result I think you want try this B10 C10 D10 E10 Start End Basic time Shift allowance 09:00 23:00 14 8 =(B10-A10)*24 In D10 =MAX(0,C10-((TIME(15,0,0)-A10)*24)) In E10 Mike "draculardw" wrote: Could one of you math wizards help me take a bite out of a math formula I am attempting to formulate. I am trying it a lot of different ways sometimes formula errors sometimes not, but still no results in the cell. This has to do with payroll calculations. Here is what I have so far that works! cells [] [09:00] - [23:00] = [14.00] this works ie: =SUM((B10-A10)*24) This is what I need help on If I am working on the clock at 15:00 I get a shift differential for those hours beyond 15:00 hours until I clock out, so lets say from the above example. I would be payed for 14 hours at my regular rate then get a diff from 15:00 - 23:00 which is a difference of 8 hours I cannot seem to come up with a formula that will allow me in another cell to calculate the two time differences and anything greater than 15:00 place in that cell the total hours of 8 any help thanks Richard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate time if greater than a given time
Mike H, it was so close to working
heres what happened Start End Basic time Shift allowance 09:00 23:00 14 8 the aboved worked here what happened when I entered a different time Start End Basic time Shift allowance 16:00 17:00 1 2 shift allowence should have also been 1 not 2 in the above the formula needs to only calculate at 15:00 hours and beyond if I am punched in Start End Basic time Shift allowance 12:00 15:00 3 0 12:00 16:00 4 1 15:00 17:00 2 2 16:00 18:00 2 2 13:00 18:00 5 3 Does this help explain it better? "Mike H" wrote: That should of course have been A10 B10 C10 D10 Start End Basic time Shift allowance 09:00 23:00 14 8 "Mike H" wrote: Hi, To get the result I think you want try this B10 C10 D10 E10 Start End Basic time Shift allowance 09:00 23:00 14 8 =(B10-A10)*24 In D10 =MAX(0,C10-((TIME(15,0,0)-A10)*24)) In E10 Mike "draculardw" wrote: Could one of you math wizards help me take a bite out of a math formula I am attempting to formulate. I am trying it a lot of different ways sometimes formula errors sometimes not, but still no results in the cell. This has to do with payroll calculations. Here is what I have so far that works! cells [] [09:00] - [23:00] = [14.00] this works ie: =SUM((B10-A10)*24) This is what I need help on If I am working on the clock at 15:00 I get a shift differential for those hours beyond 15:00 hours until I clock out, so lets say from the above example. I would be payed for 14 hours at my regular rate then get a diff from 15:00 - 23:00 which is a difference of 8 hours I cannot seem to come up with a formula that will allow me in another cell to calculate the two time differences and anything greater than 15:00 place in that cell the total hours of 8 any help thanks Richard |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use your same formula to calculate...
I used your formula to successfully calculate hours worked after 11 PM, how do you use your same formula to calculate hours worked prior to a certain time, in this case prior to 7 am?
Thank you in advance! Lisa |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use your same formula to calculate...
Hi Lisa,
Give us a clue as to what formula you refer! -- Cheers macropod [MVP - Microsoft Word] "Lisa Kerr" wrote in message ... I used your formula to successfully calculate hours worked after 11 PM, how do you use your same formula to calculate hours worked prior to a certain time, in this case prior to 7 am? Thank you in advance! Lisa |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 calculate time between 2 date/time columns | Excel Discussion (Misc queries) | |||
Calculate Ending time using Start Time and Elapsed Time | Excel Worksheet Functions | |||
calculate hours using start time & end time, excluding weekends | Excel Programming | |||
IF statement to calculate time usage in specific time bands | Excel Worksheet Functions | |||
how to calculate time start & time finish in quarter hour | Excel Discussion (Misc queries) |