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]

The second "MOD" if it is applying to when lunch or dinner starts won't matter, so no need to change it.

 

[i have a question]

The second "MOD" if it is applying to when lunch or dinner starts won't matter, so no need to change it.

Thanks a heap!
that worked!

 

[JamesCanale]

This could also work.

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	10/27/2021 14:00	10/28/2021 0:15	$ 40.00
10	10/27/2021 7:00	10/27/2021 16:00	$ 50.00
11	10/27/2021 8:00	10/27/2021 17:00	$ 25.00
12	10/27/2021 9:00	10/27/2021 18:00	$ 25.00
13	10/27/2021 10:00	10/27/2021 19:00	$ 25.00
14	10/27/2021 11:00	10/27/2021 20:00	$ 25.00
15	10/27/2021 12:00	10/27/2021 21:00	$ 50.00
16	10/27/2021 13:00	10/27/2021 22:00	$ 25.00
17	10/27/2021 14:00	10/27/2021 23:00	$ 25.00
18	10/27/2021 15:00	10/28/2021 0:00	$ 40.00
Sheet25

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