Check if a time range falls within a range

[i have a question]

Hey all,

i have a complicated question.

I need to calculate an employees pay based on the time they have worked.
Basically, the employee is paid a meal allowance if they work during a said period of time.

07:30-08:30(breakfast)=25$
12:30-13:30(lunch)=25$
19:30-20:30(dinner)=25$
23:59>(overnight)=15$

the employees starts to work at 14:00 and ends work at 00:15 the next day. So during this time the employee is entitled for a lunch,dinner and overnight. I need a formula to add these values if the employee worked between the start to the end time.

thanks a lot in advance

 

[JamesCanale]

MrExcelPlayground4.xlsx
	A	B	C
3	7:30 AM	8:30 AM	$ 25.00
4	12:30 PM	1:30 PM	$ 25.00
5	7:30 PM	8:30 PM	$ 25.00
6	11:59 PM		$ 15.00
7
8	Start Time	End Time
9	2:00 PM	12:15 AM	$ 40.00
10	7:00 AM	4:00 PM	$ 50.00
11	8:00 AM	5:00 PM	$ 25.00
12	9:00 AM	6:00 PM	$ 25.00
13	10:00 AM	7:00 PM	$ 25.00
14	11:00 AM	8:00 PM	$ 25.00
15	12:00 PM	9:00 PM	$ 50.00
16	1:00 PM	10:00 PM	$ 25.00
17	2:00 PM	11:00 PM	$ 25.00
18	3:00 PM	12:00 AM	$ 40.00
Sheet25

Cell Formulas
Range	Formula
C9:C18	C9	=IF(AND(A9<=$A$3,IF(B9<A9,B9+1,B9)>=$B$3),$C$3,0)+IF(AND(A9<=$A$4,IF(B9<A9,B9+1,B9)>=$B$4),$C$4,0)+IF(AND(A9<=$A$5,IF(B9<A9,B9+1,B9)>=$B$5),$C$5,0)+IF(AND(A9<=$A$6,IF(B9<A9,B9+1,B9)>=$A$6),$C$6,0)
A11:B18	A11	=A10+1/24

 

[i have a question]

MrExcelPlayground4.xlsx
	A	B	C
3	7:30 AM	8:30 AM	$ 25.00
4	12:30 PM	1:30 PM	$ 25.00
5	7:30 PM	8:30 PM	$ 25.00
6	11:59 PM		$ 15.00
7
8	Start Time	End Time
9	2:00 PM	12:15 AM	$ 40.00
10	7:00 AM	4:00 PM	$ 50.00
11	8:00 AM	5:00 PM	$ 25.00
12	9:00 AM	6:00 PM	$ 25.00
13	10:00 AM	7:00 PM	$ 25.00
14	11:00 AM	8:00 PM	$ 25.00
15	12:00 PM	9:00 PM	$ 50.00
16	1:00 PM	10:00 PM	$ 25.00
17	2:00 PM	11:00 PM	$ 25.00
18	3:00 PM	12:00 AM	$ 40.00
Sheet25

Cell Formulas
Range	Formula
C9:C18	C9	=IF(AND(A9<=$A$3,IF(B9<A9,B9+1,B9)>=$B$3),$C$3,0)+IF(AND(A9<=$A$4,IF(B9<A9,B9+1,B9)>=$B$4),$C$4,0)+IF(AND(A9<=$A$5,IF(B9<A9,B9+1,B9)>=$B$5),$C$5,0)+IF(AND(A9<=$A$6,IF(B9<A9,B9+1,B9)>=$A$6),$C$6,0)
A11:B18	A11	=A10+1/24

wow this looks amazing! does it work with 24hour clock format as well?

something i forgot to mention was that my start and end time cell has a date in it as well(30/09/21 21:49) and the clock format is 24hours

 

[JamesCanale]

The format of the time doesn't matter at all. If the date is in with the time, so much the better. You can actually simplify it because of that.

The IF(B9<A9,B9+1,B9) part tries to account for having no date associated with it. It could just be replaced by "B9" only whereever it appears.

 

[i have a question]

The format of the time doesn't matter at all. If the date is in with the time, so much the better. You can actually simplify it because of that.

The IF(B9<A9,B9+1,B9) part tries to account for having no date associated with it. It could just be replaced by "B9" only whereever it appears.

is it possible for me to PM you my sheet? have some sensitive info on it i'd rather not post.
I used the formula but for whatever reason it's returning the false value?

 

[JamesCanale]

I can't get strange sheets here...

I'd get the xl2bb add in for this site - and then put up a stripped version. Or just post the formula that you are actually using from one cell.

 

[i have a question]

Basically this is what my table looks like. The first image is where i base my start and end time(check-in checkout)
Image 2 where where i want my pay in $ to appear for each rows checkin and checkout time
image 3 is where i have my allowance pay and time range mentioned

 

[i have a question]

I can't get strange sheets here...

I'd get the xl2bb add in for this site - and then put up a stripped version. Or just post the formula that you are actually using from one cell.

45.7 KB file on MEGA

i'll just upload the file...if you can let me know when you had a look i can delete it..Sorry for the hassle?

 

[JamesCanale]

I think...
Use my original formula, and instead of using the plain reference to the date-time cell - say (B2), "MOD(B2,1)"

 

[i have a question]

I think...
Use my original formula, and instead of using the plain reference to the date-time cell - say (B2), "MOD(B2,1)"

I'm sorry to be a pain, do you mean =IF(AND(MOD(Sheet3!B2,1)<=Sheet2!$J$2,MOD(Sheet3!G2,1)>=Sheet2!$K$2),Sheet2!$I$2,0)+IF(AND(Sheet3!B2<=Sheet2!$J$3,Sheet3!G2>=Sheet2!$K$3),Sheet2!$I$3,0)+IF(AND(Sheet3!B2<=Sheet2!$J$4,Sheet3!G2>=Sheet2!$K$4),Sheet2!$I$4,0)+IF(AND(Sheet3!B2<=Sheet2!$J$5,Sheet3!G2>=Sheet2!$J$5),Sheet2!$I$5,0)

like the bold part? i didnt edit the entire thing as i want to be sure