I have been given an excel task which involves functions and VBA which is beyond my skills. Any help solving this would be highly appreciated
I have a table structured as shows:
So the task which I'm trying to solve is this: I have a monthly list of all the vacations our staff applied for and granted. Regarding vacation days there's a condition, that if there's a midweek holiday the middle of the vacation period that person will be then granted an extra vacation day. So far my colleagues have been looking for those special cases manually by using different filters which is tedious and slow. So the aim here is to create a macro or a formula which checks the conditions against the monthly list and tell's whether the UserID is granted an extra vacation day or not.
The data is gathered monthly. A single UserID can apply for multiple vacation periods so row A has duplicates. Also the extra vacation day has the conditions, that there needs to be at least 1 regular day before and 2 after the midweek holiday OR 2 days before and 1 after.
I assume the WORKDAY.INTL function could be used here, but to make it a bit simpler I have created the column D where the midweek holidays are manually inserted (there are usually not so many in one month, so adding them manually into the sheet is relatively fast).
So in theory what needs to be done, is first to check through duplicates in column A, merge together the vacation periods if they are consecutive, and then check if the actual vacation period meets the conditions. For example in the sample without merging A401477 doesn't meet the conditions since 29.12.2021 seems to be the same as StartDate, but when those two date ranges are put together, his/hers actual vacation period is 27.12.-31.12. and in this case 29.12. fulfills the rule of 2+1 days and will be granted an extra vacation day.
I have a table structured as shows:
| UserID | StartDate | EndDate | VacationDays |
|---|---|---|---|
| A401435 | 30.12.2021 | 31.12.2021 | 26.12.2021 |
| A401477 | 27.12.2021 | 28.12.2021 | 29.12.2021 |
| A401477 | 29.12.2021 | 31.12.2021 | |
| A403713 | 27.12.2021 | 28.12.2021 | |
| A403976 | 30.12.2021 | 31.12.2021 | |
| A405378 | 23.12.2021 | 23.12.2021 | |
| A410024 | 31.12.2021 | 31.12.2021 | |
| A410026 | 29.12.2021 | 31.12.2021 |
So the task which I'm trying to solve is this: I have a monthly list of all the vacations our staff applied for and granted. Regarding vacation days there's a condition, that if there's a midweek holiday the middle of the vacation period that person will be then granted an extra vacation day. So far my colleagues have been looking for those special cases manually by using different filters which is tedious and slow. So the aim here is to create a macro or a formula which checks the conditions against the monthly list and tell's whether the UserID is granted an extra vacation day or not.
The data is gathered monthly. A single UserID can apply for multiple vacation periods so row A has duplicates. Also the extra vacation day has the conditions, that there needs to be at least 1 regular day before and 2 after the midweek holiday OR 2 days before and 1 after.
I assume the WORKDAY.INTL function could be used here, but to make it a bit simpler I have created the column D where the midweek holidays are manually inserted (there are usually not so many in one month, so adding them manually into the sheet is relatively fast).
So in theory what needs to be done, is first to check through duplicates in column A, merge together the vacation periods if they are consecutive, and then check if the actual vacation period meets the conditions. For example in the sample without merging A401477 doesn't meet the conditions since 29.12.2021 seems to be the same as StartDate, but when those two date ranges are put together, his/hers actual vacation period is 27.12.-31.12. and in this case 29.12. fulfills the rule of 2+1 days and will be granted an extra vacation day.
