Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time guru needed
I have a relatively simple problem, but finding it complex to solve,
i'm entering start and finish times in in 24hr format (some going over 2400) and i need to manipulate these times to find, a) total hours for a job (ie start 1055 end 0130 = 2:35) b)different rates of pay for job (first hour of job charged at $40, remainder of time charged at $35) c)some jobs being charged at different rates as well (ie $40/$35 and $50/$40 and so on) I have tried; =F4-E4+IF(E4F4,1) for a) and it works fantastically but when i try to get the rest it goes wrong I tried to do an IF statement (so jobs going over 1hr i can isolate the remaining time) =IF(L4A1,L4-A1+IF(A1L4,1),0) where A1 is 1hr and L4 is result of previous calculation. this seems to work but when a job is under 1hr i get a negative result?? (so i assume IF statement not working as I expected.) Could the answer be in the cell formatting?? in the start/finish columns i'm using h:mm (entering the : is driving me nuts), in 'L' column I'm using [hh]:mm The different charge rates are going to be another IF calculation based on 'alpha's' that I havent even got to yet because solving b) is driving me insane.... Any input would be greatly appreciated |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time guru needed
GSColvin,
Assuming that your total number of hours for the job are in cell L4 (in a time format), try this formula. I think this should calculate your $40/$35 rate correctly: =IF(L4*24<=1,L4*24*40,40+(((L4*24)-1)*35)) HTH, Conan wrote in message ... I have a relatively simple problem, but finding it complex to solve, i'm entering start and finish times in in 24hr format (some going over 2400) and i need to manipulate these times to find, a) total hours for a job (ie start 1055 end 0130 = 2:35) b)different rates of pay for job (first hour of job charged at $40, remainder of time charged at $35) c)some jobs being charged at different rates as well (ie $40/$35 and $50/$40 and so on) I have tried; =F4-E4+IF(E4F4,1) for a) and it works fantastically but when i try to get the rest it goes wrong I tried to do an IF statement (so jobs going over 1hr i can isolate the remaining time) =IF(L4A1,L4-A1+IF(A1L4,1),0) where A1 is 1hr and L4 is result of previous calculation. this seems to work but when a job is under 1hr i get a negative result?? (so i assume IF statement not working as I expected.) Could the answer be in the cell formatting?? in the start/finish columns i'm using h:mm (entering the : is driving me nuts), in 'L' column I'm using [hh]:mm The different charge rates are going to be another IF calculation based on 'alpha's' that I havent even got to yet because solving b) is driving me insane.... Any input would be greatly appreciated |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time guru needed
This should calculate the amount for you
=ROUND(MIN(1,MOD(F4-E4,1)*24)*rate1+(MAX(0,MOD(F4-E4,1)*24-1)*rate2),2) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... I have a relatively simple problem, but finding it complex to solve, i'm entering start and finish times in in 24hr format (some going over 2400) and i need to manipulate these times to find, a) total hours for a job (ie start 1055 end 0130 = 2:35) b)different rates of pay for job (first hour of job charged at $40, remainder of time charged at $35) c)some jobs being charged at different rates as well (ie $40/$35 and $50/$40 and so on) I have tried; =F4-E4+IF(E4F4,1) for a) and it works fantastically but when i try to get the rest it goes wrong I tried to do an IF statement (so jobs going over 1hr i can isolate the remaining time) =IF(L4A1,L4-A1+IF(A1L4,1),0) where A1 is 1hr and L4 is result of previous calculation. this seems to work but when a job is under 1hr i get a negative result?? (so i assume IF statement not working as I expected.) Could the answer be in the cell formatting?? in the start/finish columns i'm using h:mm (entering the : is driving me nuts), in 'L' column I'm using [hh]:mm The different charge rates are going to be another IF calculation based on 'alpha's' that I havent even got to yet because solving b) is driving me insane.... Any input would be greatly appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL GURU WANTED - Time calculations | Excel Worksheet Functions | |||
I need an excel guru! HELP! | Excel Discussion (Misc queries) | |||
Need a LEN and/or CONCATENATE Guru | Excel Worksheet Functions | |||
Seeking help from a GURU | Excel Worksheet Functions | |||
I Need a math guru | Excel Discussion (Misc queries) |