Formula/VBA calculation with Time help!

[aspinray]

Hi,

I have no idea what I am doing and every time I post in here you all help me out so much and I am really grateful. So here I am again!

So, I have put tables below to try and help explain. I have two reports, one that pulls dates for holidays, the date, name of the person, and if it is a full day or not. I then have another report that shows shifts for that person day to day, with start and end times and lunch times.

What is need is for the holiday report to show paid hours, minus lunch breaks, at the end of each row. The only way i think think to do this is by looking at the second report i have this is the shift report, mentioned above. So, match the persons name and date on both reports, look at start and end time, 08.00-16.00, then work out paid hours. Problem I have is everyone has different lunches and therefore the report will say 36 mins @ 4hrs 15 mins or 30 mins @ 3 hrs. As there are 1000s of requests i wondered if there was anything I could do to make this process automatic. So, the macro or formula once its matched name and date, figures the shift hours out then looks at the lunch and takes this off the total hours - so 08.00 - 16.00 is 08.00 hours minus 36 mins lunch is 07.24.

Finally, i need to only take the lunch into account if the shift is over 6 hours, otherwise the lunch doesn't get taken into account.

I am not sure how to attach excel documents, or if you can.. but below is what the reports look like.

Please let me know if you need any more information, thanks in advance!

[TABLE="width: 1032, align: left"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 3"]Holiday Report[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Date Period:[/TD]
[TD="colspan: 2"]05/06/17 - 31/12/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]I need this to populate automatically[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[TD]j[/TD]
[TD]k[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Site[/TD]
[TD]Time zone[/TD]
[TD]Team[/TD]
[TD]Agent[/TD]
[TD]Date[/TD]
[TD]Type[/TD]
[TD]Description[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Status[/TD]
[TD]Paid Hours[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]NE- Enhance[/TD]
[TD]GMT[/TD]
[TD]AB - Joe Smith[/TD]
[TD]Name Surname[/TD]
[TD]05/06/2017[/TD]
[TD]Full Day Exception[/TD]
[TD]First Choice Holiday Full Day[/TD]
[TD]Full Day[/TD]
[TD]Full Day[/TD]
[TD]Granted[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]NE- Enhance[/TD]
[TD]GMT[/TD]
[TD]AB - Joe Smith[/TD]
[TD]Name Surname[/TD]
[TD]06/06/2017[/TD]
[TD]Full Day Exception[/TD]
[TD]First Choice Holiday Full Day[/TD]
[TD]Full Day[/TD]
[TD]Full Day[/TD]
[TD]Granted[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]NE- Enhance[/TD]
[TD]GMT[/TD]
[TD]AB - Joe Smith[/TD]
[TD]Name Surname[/TD]
[TD]07/06/2017[/TD]
[TD]Full Day Exception[/TD]
[TD]First Choice Holiday Full Day[/TD]
[TD]Full Day[/TD]
[TD]Full Day[/TD]
[TD]Granted[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]NE- Enhance[/TD]
[TD]GMT[/TD]
[TD]AB - Joe Smith[/TD]
[TD]Name Surname[/TD]
[TD]08/06/2017[/TD]
[TD]Full Day Exception[/TD]
[TD]First Choice Holiday Full Day[/TD]
[TD]Full Day[/TD]
[TD]Full Day[/TD]
[TD]Granted[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]NE- Enhance[/TD]
[TD]GMT[/TD]
[TD]AB - Joe Smith[/TD]
[TD]Name Surname[/TD]
[TD]09/06/2017[/TD]
[TD]Full Day Exception[/TD]
[TD]First Choice Holiday Full Day[/TD]
[TD]Full Day[/TD]
[TD]Full Day[/TD]
[TD]Granted[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]NE- Enhance[/TD]
[TD]GMT[/TD]
[TD]AB - Joe Smith[/TD]
[TD]Name Surname[/TD]
[TD]14/06/2017[/TD]
[TD]Full Day Exception[/TD]
[TD]First Choice Holiday Full Day[/TD]
[TD]Full Day[/TD]
[TD]Full Day[/TD]
[TD]Granted[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]NE- Enhance[/TD]
[TD]GMT[/TD]
[TD]AB - Joe Smith[/TD]
[TD]Name Surname[/TD]
[TD]15/06/2017[/TD]
[TD]Full Day Exception[/TD]
[TD]First Choice Holiday Full Day[/TD]
[TD]Full Day[/TD]
[TD]Full Day[/TD]
[TD]Granted[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]NE- Enhance[/TD]
[TD]GMT[/TD]
[TD]AB - Joe Smith[/TD]
[TD]Name Surname[/TD]
[TD]16/06/2017[/TD]
[TD]Full Day Exception[/TD]
[TD]Holiday Full Day[/TD]
[TD]Full Day[/TD]
[TD]Full Day[/TD]
[TD]Granted[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

	Date Period:
	a	b	c	d	e	f	g	h	i	j
1	Site	Time zone	Team	Agent	Date	Type	Description	Start Time	End Time	Status
2	NE- Enhance	GMT	AB - Joe Smith	Name Surname	05/06/2017	Rotating Pattern (Granted Fixed Shift)	4-10hrs 36mins @ 4hrs 15 mins	09:00	17:00	Granted
3	NE- Enhance	GMT	AB - Joe Smith	Name Surname	06/06/2017	Rotating Pattern (Granted Fixed Shift)	4-10hrs 36mins @ 4hrs 15 mins	09:00	17:00	Granted
4	NE- Enhance	GMT	AB - Joe Smith	Name Surname	07/06/2017	Rotating Pattern (Granted Fixed Shift)	4-10hrs 36mins @ 4hrs 15 mins	09:00	17:00	Granted
5	NE- Enhance	GMT	AB - Joe Smith	Name Surname	08/06/2017	Rotating Pattern (Granted Fixed Shift)	4-10hrs 36mins @ 4hrs	10:00	14:00	Granted
6	NE- Enhance	GMT	AB - Joe Smith	Name Surname	09/06/2017	Rotating Pattern (Granted Fixed Shift)	4-10hrs 36mins @ 3hrs 24 mins	12:00	20:00	Granted
7	NE- Enhance	GMT	AB - Joe Smith	Name Surname	10/06/2017	Rotating Pattern (Day Off)	Day Off	Full Day	Full Day	Granted
8	NE- Enhance	GMT	AB - Joe Smith	Name Surname	11/06/2017	Rotating Pattern (Day Off)	Day Off	Full Day	Full Day	Granted

<tbody>
[TD="colspan: 3"][/TD]

[TD="colspan: 3"]Shift Report[/TD]

[TD="colspan: 2"]05/06/17 – 31/12/17[/TD]

</tbody>

 

[jorismoerings]

Hi,

Not VBA but with formula. Take a look at this:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F	G	H	I	J	K
1	Site	Time zone	Team	Agent	Date	Type	Description	Start Time	End Time	Status	Paid Hours
2	NE- Enhance	GMT	AB - Joe Smith	Name Surname	5-6-2017	Full Day Exception	First Choice Holiday Full Day	Full Day	Full Day	Granted	07:24
3	NE- Enhance	GMT	AB - Joe Smith	Name Surname	6-6-2017	Full Day Exception	First Choice Holiday Full Day	Full Day	Full Day	Granted	07:24
4	NE- Enhance	GMT	AB - Joe Smith	Name Surname	7-6-2017	Full Day Exception	First Choice Holiday Full Day	Full Day	Full Day	Granted	07:24
5	NE- Enhance	GMT	AB - Joe Smith	Name Surname	8-6-2017	Full Day Exception	First Choice Holiday Full Day	Full Day	Full Day	Granted	00:00
6	NE- Enhance	GMT	AB - Joe Smith	Name Surname	9-6-2017	Full Day Exception	First Choice Holiday Full Day	Full Day	Full Day	Granted	07:24
7	NE- Enhance	GMT	AB - Joe Smith	Name Surname	14-6-2017	Full Day Exception	First Choice Holiday Full Day	Full Day	Full Day	Granted	00:00
8	NE- Enhance	GMT	AB - Joe Smith	Name Surname	15-6-2017	Full Day Exception	First Choice Holiday Full Day	Full Day	Full Day	Granted	00:00
9	NE- Enhance	GMT	AB - Joe Smith	Name Surname	16-6-2017	Full Day Exception	Holiday Full Day	Full Day	Full Day	Granted	00:00
holiday report

Cell Formulas
Range	Formula
K2	{=IFERROR(IF(INDEX('Shift report'!$G$2:$G$8,MATCH(C2&D2&E2,'Shift report'!$C$2:$C$8&'Shift report'!$D$2:$D$8&'Shift report'!$E$2:$E$8,0))="Full Day",0,((INDEX('Shift report'!$I$2:$I$8,MATCH(C2&D2&E2,'Shift report'!$C$2:$C$8&'Shift report'!$D$2:$D$8&'Shift report'!$E$2:$E$8,0)))-INDEX('Shift report'!$H$2:$H$8,MATCH(C2&D2&E2,'Shift report'!$C$2:$C$8&'Shift report'!$D$2:D8&'Shift report'!$E$2:$E$8,0)))-IF(((INDEX('Shift report'!$I$2:$I$8,MATCH(C2&D2&E2,'Shift report'!$C$2:$C$8&'Shift report'!$D$2:$D$8&'Shift report'!$E$2:$E$8,0)))-INDEX('Shift report'!$H$2:$H$8,MATCH(C2&D2&E2,'Shift report'!$C$2:$C$8&'Shift report'!$D$2:$D$8&'Shift report'!$E$2:$E$8,0)))>(LEFT(INDEX('Shift report'!$G$2:$G$8,MATCH(C2&D2&E2,'Shift report'!$C$2:$C$8&'Shift report'!$D$2:$D$8&'Shift report'!$E$2:$E$8,0)),1)/24),VALUE(MID(INDEX('Shift report'!$G$2:$G$8,MATCH(C2&D2&E2,'Shift report'!$C$2:$C$8&'Shift report'!$D$2:$D$8&'Shift report'!$E$2:$E$8,0)),SEARCH("Hrs ",INDEX('Shift report'!$G$2:$G$8,MATCH(C2&D2&E2,'Shift report'!$C$2:$C$8&'Shift report'!$D$2:$D$8&'Shift report'!$E$2:$E$8,0)),1)+4,2)),VALUE(MID(INDEX('Shift report'!$G$2:$G$8,MATCH(C2&D2&E2,'Shift report'!$C$2:$C$8&'Shift report'!$D$2:$D$8&'Shift report'!$E$2:$E$8,0)),SEARCH("@*Hrs ",INDEX('Shift report'!$G$2:$G$8,MATCH(C2&D2&E2,'Shift report'!$C$2:$C$8&'Shift report'!$D$2:$D$8&'Shift report'!$E$2:$E$8,0)),1)+7,2)))/(60*24)),0)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Beware: Cell K5 returns 0 as there's no minutes mentioned in the Shift Report cell G5 for up to 4 hrs