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!
<tbody>
</tbody>
<tbody>
</tbody>
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!
| Holiday Report | |||||||||||
| Date Period: | 05/06/17 - 31/12/17 | I need this to populate automatically | |||||||||
| 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 | 05/06/2017 | Full Day Exception | First Choice Holiday Full Day | Full Day | Full Day | Granted | |
| 3 | NE- Enhance | GMT | AB - Joe Smith | Name Surname | 06/06/2017 | Full Day Exception | First Choice Holiday Full Day | Full Day | Full Day | Granted | |
| 4 | NE- Enhance | GMT | AB - Joe Smith | Name Surname | 07/06/2017 | Full Day Exception | First Choice Holiday Full Day | Full Day | Full Day | Granted | |
| 5 | NE- Enhance | GMT | AB - Joe Smith | Name Surname | 08/06/2017 | Full Day Exception | First Choice Holiday Full Day | Full Day | Full Day | Granted | |
| 6 | NE- Enhance | GMT | AB - Joe Smith | Name Surname | 09/06/2017 | Full Day Exception | First Choice Holiday Full Day | Full Day | Full Day | Granted | |
| 7 | NE- Enhance | GMT | AB - Joe Smith | Name Surname | 14/06/2017 | Full Day Exception | First Choice Holiday Full Day | Full Day | Full Day | Granted | |
| 8 | NE- Enhance | GMT | AB - Joe Smith | Name Surname | 15/06/2017 | Full Day Exception | First Choice Holiday Full Day | Full Day | Full Day | Granted | |
| 9 | NE- Enhance | GMT | AB - Joe Smith | Name Surname | 16/06/2017 | Full Day Exception | Holiday Full Day | Full Day | Full Day | Granted |
<tbody>
</tbody>
| Shift Report | ||||||||||
| Date Period: | 05/06/17 – 31/12/17 | |||||||||
| 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>
</tbody>
