Incorporate a condition in an IF((OR €¦.formula
These are the answers I get according to the formulas used below in order of
sequence
G6 E6
10:00 €“ 20:00 = 10
No matter what time I put above 18:00 answer gives = 8 This is correct
Placed in cell AG6
=(MIN(TIMEVALUE("18:00"),IF(G6<E6,G6+1,G6))-MAX(TIMEVALUE("6:00"),E6))*24
G6 E6
10:00 €“ 20:00
Adjusts to times giving the amount above 18:00 answer gives = 2 This is
correct
Placed in cell AH6
=IF((OR(G6="",E6="")),0,IF((G6<E6),((G6-E6)*24)+24,(G6-E6)*24))-(MIN(TIMEVALUE("18:00"),IF(G6<E6,G6+1,G6))-MAX(TIMEVALUE("6:00"),E6))*24
G6 E6
10:00 €“ 20:00
Show the subtracted times from the total time of 10 hours answer gives = 12
and this is also correct
Placed in cell AI6
=IF((OR(G6="",E6="")),0,IF((G6<E6),((G6-E6)*24)+24,(G6-E6)*24))-(IF(G6<E6,G6+1,E6)-G6)*24-AH6
Thank you very much it is what I wanted
"Luke M" wrote:
Your initial conditions have time overlaps, but I'm thinking your two time
rates are
Rate1: 06:00 - 18:00
and
Rate2: 18:01 - 05:59
Assuming your data is in A2 and B2, here's formula for Rate1, giving hours
as an integer:
=(MIN(TIMEVALUE("18:00"),IF(B2<A2,B2+1,B2))-MAX(TIMEVALUE("6:00"),A2))*24
Formula for Rate2, assuming previous formula is in C2:
=(IF(B2<A2,B2+1,B2)-A2)*24-C2
If I have assumed incorrectly as to your rate times, adjust accordingly in
formula 1.
--
Best Regards,
Luke M
*Remember to click "yes" if this post helped you!*
"aussiegirlone" wrote:
As Im still learning a lot€¦. I have a Query below to calculate rates per
hour:
Anything between 18:01 and 06:00 is one rate and anything between 00:00 and
06:00 is another rate
=IF((OR(G6="",E6="")),0,IF((G6<E6),((G6-E6)*24)+24,(G6-E6)*24))
Example Times
10:00 - 14:00 = 4 hours
10:00 - 18:00 = 8 hours
10:00 - 19:01 = 9.016666667 hours
Incorporate IF((OR formula above with this condition below
Then Subtract all that is equal to or less than 18:01 and the answer to
10:00-19:01 should be 1.016666667 hours
Example 2
10:00 - 20:00 = 10 should now = 2 hours
Another example
10:00 - 00:00 = 14 should now = 6 hours
Most important example as there are two different rates in this one.
00:00 - 10:00 = 10 should now be = 6 hours of one rate and 4 hours of
another rate
Each rate having its own cell.
Would some one like to help with this?
|