For a while I have been trying to create a excel sheet, in which a user can input different personnel vacation data and from that data Excel is supposed to form an array of dates and based on some specific conditions it should return in the last column whether the person is granted an extra vacation day.
The sheet is supposed to work in an a way that the user inserts the personnel data into columns A-C and then column O gives a boolean output depending on whether the specific conditions are met for the extra vacation day or not. As a result column O should be TRUE if one of these conditions are met:
- The person has 3 consecutive vacation days right before any of the days listed in column E
- The person has 2 consecutive vacation days right before any of the days listed in column E and 1 right after it
- The person has a vacation day right before any of the days listed in column E and 2 consecutive days right after it
So originally with the help of people over here I decided to solve this by forming an array of dates for each user. For each row a date range is formed based on the values in columns B and C and if the UserID is the same as in the previous row then those date ranges are added up creating an array, in which all the days when the person is on vacation are listed. This array is in column J. Then the functions in rows K-M check whether the array has 4 consecutive days and checking for conditions listed above and if any one of the conditions are met, then it returns TRUE and highlights the row for convenience (for some reason besides the 2nd row they all seem to display FALSE even though they seem to function as intended).
There's just one problem which I didn't take under consideration beforehand: weekends. When counting consecutive days I should've been only counting workdays. I've been trying to solve this issue in columns F-H by using WEEKDAY-function and various conditions to check if the end date is Friday and then expanding the original end date into Sunday to have those days also included into the array, but I've realized that this approach is way too complex and prone to bugs. The WORKDAY-function could be the solution to begin with, but since the dates in the array are converted into text format, the use of WORKDAY-function is problematic also.
I've been trying to solve this issue the whole week, but I feel like I'm way beyond my league here. Any help from people smarter than me is again highly appreciated.
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F2:F32 | F2 | =IF(COUNTIFS($E$2:$E$101, C2+1) > 0, C2+1, C2) |
| G2:G32 | G2 | =IF(AND(B2=C2, B3=C3, A1<>A2, A3<>A4), F2, IF(AND(A2=A3, WEEKDAY(F2, 2)=5, WEEKDAY(B3, 2)=1, B3-F2<=3, IF(B2<>OFFSET(B2, -1, 0), B3<>OFFSET(C3, -1, 0), TRUE), OR(A1<>A2, A4<>A2)), F2+2, F2)) |
| H2:H32 | H2 | =IF(AND(WEEKDAY(G2, 3) = 5, A2 = A3, F3 = WORKDAY(G2, 3), F3 - G2 <= 3, OR(B2 <> C2, B3 <> C3)), G2 + 2, G2) |
| I2:I32 | I2 | =TEXTJOIN("; ", TRUE, SEQUENCE(G2-B2+1, 1, B2)) |
| J2:J32 | J2 | =TEXTJOIN("; ",TRUE,IF($A$2:$A$5203=A2,I$2:I$5001,"")) |
| K2:K32 | K2 | =IF(C2-B2>2,OR(BYROW(E$2:E$100,LAMBDA(rw,ISNUMBER(FIND(TEXTJOIN("; ",,SEQUENCE(4,,rw-1)),J2)))),FALSE)) |
| L2:L32 | L2 | =IF(C2-B2>2,OR(BYROW(E$2:E$100,LAMBDA(rw,ISNUMBER(FIND(TEXTJOIN("; ",,SEQUENCE(4,,rw-2)),J2)))),FALSE)) |
| M2:M32 | M2 | =IF(C2-B2>2,OR(BYROW(E$2:E$100,LAMBDA(rw,ISNUMBER(FIND(TEXTJOIN("; ",,SEQUENCE(4,,rw-3)),J2)))),FALSE)) |
| N2:N32 | N2 | =OR(OR(BYROW(E$2:E$100,LAMBDA(rw,ISNUMBER(FIND(TEXTJOIN("; ",,SEQUENCE(4,,rw-1)),J2))))), OR(BYROW(E$2:E$100,LAMBDA(rw,ISNUMBER(FIND(TEXTJOIN("; ",,SEQUENCE(4,,rw-2)),J2))))), OR(BYROW(E$2:E$100,LAMBDA(rw,ISNUMBER(FIND(TEXTJOIN("; ",,SEQUENCE(4,,rw-3)),J2)))))) |
| O2:O32 | O2 | =IF(N2=TRUE, TRUE, "") |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| A2:C5000 | Expression | =$O2=TRUE | text | NO |
| O2:O4799 | Expression | =$O2=TRUE | text | NO |
The sheet is supposed to work in an a way that the user inserts the personnel data into columns A-C and then column O gives a boolean output depending on whether the specific conditions are met for the extra vacation day or not. As a result column O should be TRUE if one of these conditions are met:
- The person has 3 consecutive vacation days right before any of the days listed in column E
- The person has 2 consecutive vacation days right before any of the days listed in column E and 1 right after it
- The person has a vacation day right before any of the days listed in column E and 2 consecutive days right after it
So originally with the help of people over here I decided to solve this by forming an array of dates for each user. For each row a date range is formed based on the values in columns B and C and if the UserID is the same as in the previous row then those date ranges are added up creating an array, in which all the days when the person is on vacation are listed. This array is in column J. Then the functions in rows K-M check whether the array has 4 consecutive days and checking for conditions listed above and if any one of the conditions are met, then it returns TRUE and highlights the row for convenience (for some reason besides the 2nd row they all seem to display FALSE even though they seem to function as intended).
There's just one problem which I didn't take under consideration beforehand: weekends. When counting consecutive days I should've been only counting workdays. I've been trying to solve this issue in columns F-H by using WEEKDAY-function and various conditions to check if the end date is Friday and then expanding the original end date into Sunday to have those days also included into the array, but I've realized that this approach is way too complex and prone to bugs. The WORKDAY-function could be the solution to begin with, but since the dates in the array are converted into text format, the use of WORKDAY-function is problematic also.
I've been trying to solve this issue the whole week, but I feel like I'm way beyond my league here. Any help from people smarter than me is again highly appreciated.
