Welcome to the Board! One idea is shown below. You didn't say if you have the Source 2 table available in an Excel worksheet. Assuming you do, a helper column could be added that describes the lower threshold of each time range in what I'll call the 99ymmdd format. In other words, 99 followed by the number of years of leave, the number of months of leave, and the number of days of leave. Because the number of months and days may have one or two digits, we require that two characters be used for each (e.g., 2 months would be 02). I'm assuming the number of years of leave will rarely exceed 1 (if any?)...and certainly not more than 9, so only one character is needed for years. And in most cases, the number of years will be 0. To avoid an issue where Excel will want to drop this leading 0, I've prepended "99" to the string of numbers (text). So 0 years, 6 months, 18 days of leave would be 9900618. This gives us a number that is readily compared with the lookup Source 2 table, assuming we establish the helper column mentioned above. Then a straightforward XLOOKUP will return the desired result. The primary challenge is creating the number describing the length of leave in 99ymmdd format.
If you are being given the length of leave, then the precise format needs to be known (and preferably consistent). If you are calculating the length of leave in the worksheet, then it would be advantageous to construct the 99ymmdd format directly as each component (years, months, and days) is determined. In this example, I'm assuming you are given (or can be given) the format as "a Y b M c D" and we need to extract a, b, and c from the text string, determine how many characters they are, pad the front of them with a 0 where necessary and finally form the 99ymmdd format. This is the messiest part.
Incidentally, I worked up some formulas for constructing the "a Y b M c D" expression and did not get the same result for the dates in your example (different by two days), but I don't know the details of your calculations (e.g., are holidays, weekends, and perhaps other types of days to be counted). So the primary focus in the following example should begin at cell B7 and then jump to B10 to see how the relevant parts are extracted to create the lookup value. The Source 2 table here includes the lookup column, which is the lower threshold for each bracket expressed in 99ymmdd format. Finally, C10 is the XLOOKUP function that determines which time bracket the lookup value falls within...and the corresponding text from the "Carry forward" column is returned.
If you are not familiar with the XL2BB add-in used to post this working example, you can install it by following the link (see my signature block for a link). You should be able to click on the clipboard copy icon in the upper left of this example (intersection of row/column headings) and paste it into your worksheet (suggested paste into cell A1 to match the same upper left cell as the example)...and then give it a try to see if it returns the expected results.
| MrExcel_20220401.xlsx |
|---|
|
|---|
| A | B | C |
|---|
| 1 | Source 1 | | |
|---|
| 2 | Leave start | 27/09/2021 | |
|---|
| 3 | Leave end | 02/04/2022 | |
|---|
| 4 | Length of leave: | 0 | Y |
|---|
| 5 | | 6 | M |
|---|
| 6 | | 1 | D |
|---|
| 7 | Length of leave (one line) | 0 Y 6 M 1 D | |
|---|
| 8 | | | |
|---|
| 9 | Results | Length of Leave 99ymmdd format | Carry forward |
|---|
| 10 | | 9900601 | 21 days/157.5 hours |
|---|
| 11 | | | |
|---|
| 12 | Source 2 | | |
|---|
| 13 | Length of Leave | 99ymmdd format | Max number of days/hours carry forward |
|---|
| 14 | 3 months or less | 9900000 | 5 days/37.5 hours |
|---|
| 15 | 3 months + 1day to 6 months | 9900301 | 14 days/105 hours |
|---|
| 16 | 6 months + 1 day to 9 months | 9900601 | 21 days/157.5 hours |
|---|
| 17 | more than 9 months + 1 day | 9900901 | 28 days/210 hours |
|---|
|
|---|
