Re: time guru needed
- From: "Bob Phillips" <bob.NGs@xxxxxxxxxxxxx>
- Date: Thu, 24 Jan 2008 13:12:12 -0000
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)
<GSColvin@xxxxxxxxx> wrote in message
news:c80c0ec0-403f-4172-b6de-d4e07c7403f0@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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
.
- References:
- time guru needed
- From: GSColvin
- time guru needed
- Prev by Date: Re: Formulas in 2007 not working in 2003
- Next by Date: Re: conditional formatting for <= +- 1.0%
- Previous by thread: Re: time guru needed
- Next by thread: Re: if range b6:n6 has no content, return A6
- Index(es):
Relevant Pages
|
