Calculate meal allowances

[Manolocs]

Hello, How can I calculate the total of each allowance the employee is entitle during the given period?

Start Time: 22/Mar/17 12:50
End Time: 30/Mar/17 18:50
Breakfast Between: 7:30 and 9:30
Lunch between: 12:30 and 14:30
Dinner between: 19:30 and 21:30

Thanks in advance

 

[J.Ty.]

Hi,

I am not sure what you are asking about. What is the total allowance? Do you mean time, number of meals, money, something else?

Next, I would like to see how the data in your worksheet is organized, how much space can be occupied by the calculation, etc.

J.Ty.

 

[Manolocs]

Yes J.Ty, the total number of breakfast dinner and lunch no money involved only the data I show. it is preferred to be done in formulas, I am using MAC computer. Column A = name, B= Start time, C=End Time, D=Breakfast, E=Lunch, F=Dinner. other than this is available to calculations.

 

[J.Ty.]

Ok, I can try counting the number of meals. What happens if, say, End Time is in the middle of a meal break (say, at 19:50)? Should I count the last dinner then or not? The same question applies to Start TIme.

J.Ty.

 

[Manolocs]

If the employee end work at 19:31 he has right a dinner allowance, this is valid for all times...
Thanks for your help J.Ty.

 

[Tetra201]

Try these:

For the number of breakfasts =(MOD($C2,1)>--"7:30")+(MOD($B2,1)<--"9:30")+INT($C2)-INT($B2)-1
For the number of lunches =(MOD($C2,1)>--"12:30")+(MOD($B2,1)<--"14:30")+INT($C2)-INT($B2)-1
For the number of dinners =(MOD($C2,1)>--"19:30")+(MOD($B2,1)<--"21:30")+INT($C2)-INT($B2)-1

 

[Manolocs]

Works perfect, thank you very much, tried for long time .

 

[J.Ty.]

Another solution.

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F	G	H	I	J
1	Start time	End time	Meals Full days	Meal first day	Meals last day	Total meals	Help	Breakfast	Lunch	Dinner
2			-3	3	0	0	0	07:30:01	12:30:01	19:30:01
3							0	09:30:00	14:30:00	21:30:00
Sheet7

Cell Formulas
Range	Formula
C2	=(DAYS(B2,A2)-1)*3
D2	=4-MATCH(TIME(HOUR(A2),MINUTE(A2),0),$G$3:$J$3)
E2	=MATCH(TIME(HOUR(B2),MINUTE(B2),0),$G$2:$J$2)-1
F2	=C2+D2+E2
H2	=TIME(7,30,1)
H3	=TIME(9,30,0)
I2	=TIME(12,30,1)
I3	=TIME(14,30,0)
J2	=TIME(19,30,1)
J3	=TIME(21,30,0)

 

[jjj8888]

Hi J.Ty,

Firstly, I'm hoping this thread is still monitored. I'm looking to create a spreadsheet very similar to the one above, but to track which allowances are due based on times of the day worked.

- Breakfast, Lunch and Dinner times and amounts should be specified and form part of the base reference.
- There should also be an "incidentals" amount added if the period worked is 24 hours or more.
- Start and end time and therefore would need both date and time function to calculate which meals are owed as well as any "incidental" allowance as per the above.
- A total for each shift (specified by start and finish date & time) would show the total amount for that shift.

Any assistance would be apprecaited.