time guru needed

[GSColvin]

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(E4>F4,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(L4>A1,L4-A1+IF(A1>L4,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

 

[Conan Kelly]

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

 

[Bob Phillips]

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)