Meal Allowance Reimbursement calculation

[pajaropr]

I am in need of a Meal Allowance Reimbursement Template that calculates based on the information what allowances are due to the traveler.

Breakfast $6
Lunch $11
Dinner $19
Total for day $36

Meals for first and last days of Domestic Travel are based on times of departure and return. On first and last days, travel must begin before 6am and extend past 8am to qualify for breakfast; begin before Noon and extend past 2pm to qualify for lunch; and begin before 6pm and extend past 8pm to qualify for dinner.

Thank you.

 

[JamesCanale]

MrExcelPlayground18.xlsx
	A	B	C	D
1	B	L	D
2	6:00 AM	12:00 PM	6:00 PM
3	8:00 AM	2:00 PM	8:00 PM
4	$6.00	$11.00	$19.00
5
6	Start	Monday 6/26/2023 6:00 AM
7	Finish	Friday 6/30/2023 2:00 PM
8
9	Meal:	$161.00
Sheet10

Cell Formulas
Range	Formula
B9	B9	=LET(s,B6,f,B7,sm,A2:C2,fm,A3:C3,mp,A4:C4,a,SEQUENCE(INT(f)-INT(s)+1,1,INT(s)),b,a+sm,c,a+fm,d,IF((b>=s)*(c<=f),mp,0),SUM(d))

 

[pajaropr]

MrExcelPlayground18.xlsx
	A	B	C	D
1	B	L	D
2	6:00 AM	12:00 PM	6:00 PM
3	8:00 AM	2:00 PM	8:00 PM
4	$6.00	$11.00	$19.00
5
6	Start	Monday 6/26/2023 6:00 AM
7	Finish	Friday 6/30/2023 2:00 PM
8
9	Meal:	$161.00
Sheet10

Cell Formulas
Range	Formula
B9	B9	=LET(s,B6,f,B7,sm,A2:C2,fm,A3:C3,mp,A4:C4,a,SEQUENCE(INT(f)-INT(s)+1,1,INT(s)),b,a+sm,c,a+fm,d,IF((b>=s)*(c<=f),mp,0),SUM(d))

Thank you!!!!