Thread: Separating time-intervals

Hi there. I have a question, I hope some of you can help me with.

I want to create an excel-sheet to calculate a salary.

I would like 3 columns:

A: Meeting time
B: Got off
C: Salary

The tricky thing is that there is a different payment for different time-intervals.

From 06.00 to 08.00: 100
From 09:00 to 17:00: 150
From 18:00 to 24:00: 200

So lets say I type:___ A (Meeting time)___B (Got off)__Salary____Example number
__________________07:00___________18:00______?________1____________
__________________23:00___________09:00______?________ 2___________

How do I make a formula that can figure out what the total salary is? Because in example 1, you have 1 hour to 100/hour, 7 hours to 150/hour and 1 hour to 200/hour.
In example 2, you also have to deal with to fact that it can create some difficulties when you go from 23:00 to 09.00

Really hope some of you can give me a hint to solve this problem.

Thanks!

Not sure I fully nderstand the salary bit but I hope the this helps.

To get the time, add this to C2

=IF(B2-A2>0,B2-A2,B2+1-A2)

or 

=IF(B2-A2>0,B2-A2,B2+1-A2) * 24

Assuming that there are salary bands which relate to the length of the shift, in D2 add

=IF(C2<=1,C2*24*100,IF(C2<=7,C2*24*150,C2*24*200))

or 

=IF(C2<=1,C2*100,IF(C2<=7,C2*150,C2*200))