Calculating if Meal Allowance applies based on time out of the office

[Jaypee666]

Hi All,

I have what is again probably another one for the Excel gurus on this form, but I am currently about to lob a laptop out a window trying to get this to work.

below should show an example of a sheet currently being used.
Ultimately what I am trying, (in vain) to get to work is if the total time on the road on any given date is over 5 hours that the Meal Allowance in R4 will be applied and if over 10 hours R3 will be applied
For now though I would be happy with getting a simply Yes/No response if applicable or not.

Looking forward to your responses, and thanking you in advance.

Expenses sheet 15.06.23- 28.06.23.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Company Expenses Claim Form
2
3	ALLOWANCE SHEET		15th June - 28th June '23															39.08	more 10 hours
4																		16.29	more 5 hours
5	EMPLOYEE:-		Employee 789665
6
7
8	DATE	FROM	TO	REASON	RETURN?	TIME DEPARTED	TIME ARRIVED	JOB COMPLETE	RETURN TO OFFICE	KMS BAND 1	KMS BAND 2	KMS BAND 3	KMS BAND 4	MILEAGE	TOLLS		TIME AWAY	MEALS
9										0.5182	0.9063	0.3922	0.2587	TOTAL	ALLOWANCE			ALLOWANCE
10
11	20/06/2023	Office	Client 1	On-Site Issue	Y	11:50:00	12:25:00	12:35:00	13:25:00			58.00				No	01:35
12	21/06/2023	Office	Client 2	On-Site Issue	Y	10:30:00	11:00:00	11:40:00	12:10:00			58.00				No	01:40
13	21/06/2023	Office	Client 3	On-Site Issue	Y	15:25:00	16:00:00	16:10:00	17:00:00			58.00				No	01:35
14	28/06/2023	Office	Client 4	On-Site Issue	N	10:55:00	11:13:00	11:25:00				12.00				No	00:30
15	28/06/2023	Client 4	Client 5	On-Site Issue	N	11:25:00	13:15:00	14:00:00				16.00				No	02:35
16	28/06/2023	Client 5	Client 6	On-Site Issue	Y	14:05:00	14:45:00	16:00:00	17:00:00			60.00				No	02:55	16.29
17
18
19
20
Main

Cell Formulas
Range	Formula
P11:P16	P11	=IF(SUMIFS(Q11:Q26, A11:A26, A11) > TIME(5,0,0), "Yes", "No")
Q11:Q16	Q11	=IF(I11="", TEXT(MOD((H11-F11+1), 1), "hh:mm"), TEXT(MOD((I11-F11+1), 1), "hh:mm"))

 

[ldonkers]

Sorry for the confusion. I tried something but it did not work. I have removed it. Not sure how to delete my reply.

 

[ldonkers]

If you just take the difference between the return to office time and the time departed like this:
Time Away (Q11)
=I11-F11
the Meal Allowance formula like this should work.
=IF(HOUR(Q11)>=5,"Yes","No")

 

[Jaypee666]

@ldonkers, I should have mentioned, this is a sheet I was asked to take a look at and the formulas in it were what I was given.

I was looking at a simple formula like I11-F11 in Q11 and that would be fine if there was only 1 trip or site visit in the day, but as you can see in the example, on the 28th there were 3 seperate visits, that while individually don't merit a Meal Allowance, combined they do as it was one trip.

 

[ldonkers]

I was looking at your data. It looks like the data can be entered as many trips on a single day. This could make the formulas hard to manage. You may want to consider the following approach: Keep you data as is but create another table below that will total by day. You can then have your mileage totals, meal expenses calculated without changing any formulas.

I would add the Time away with the following formula:
=IF(E11="y",I11-F11,H11-F11)



This would take of the return to office issue and you can copy that formula down.

I would create the subtotals table like below.



In the time away you can the use the following to get sum for that day. This will take all the time for a day and total them together.

=SUMIF($A$11:$A$16,A23,$P$11:$P$16)



You can then apply the following formula for the meal allowance.

=IF(HOUR(C23)>=5,"Yes","No")




You can use the same idea to get the totals for mileage etc.