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?

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?

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?