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

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

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