View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
[email protected] GSColvin@gmail.com is offline
external usenet poster
 
Posts: 1
Default 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