Lookup and Count Array Formula Query

Morning all. Struggling to figure out a formula that does everything that I need it to. I'm almost certain this is possible, but as there's several parts to this formula, I'm struggling to wrap my head around it.

So, I have a 3x tables. In one table (the table name is Tbl_Att), I have 4x columns. Let's call this the Attendance Table. One is headed "Employee", and is a list of unique Employee names. Then the next two columns are headed "From Date" and "To Date". These contain a date range. The final column, is headed "HR Absences". It is this column that I would like help creating a formula for. Let's call this the Formula Column.

In another table, which is called Tbl_PHs, there is just 1x column headed "Date", and this contains a list of dates. These are dates for UK Public Holidays. Let's call this table the Public Holidays Table.

The third table is titled Tbl_Abs. Let's call this the Absences Table. This contains 4x columns. The first is headed "Full Name". The next is headed "Duration". The final two are headed "Request Start Date" and "Request End Date". These contain a date range.

What I would like, is a formula to put in the Formula Column, which does the following:

• Looks for the Employee name (from the Attendance Table) in the Full Name column of the Absences Table.
• Checks whether any of the dates falling within the date range on the Attendances Table are found within the data range (for that Employee) on the Absences Table. This then needs to somehow return an array of all of those dates that overlap between the ranges (i.e. if in the Attendance Table for a particular Employee, there was a date range of 1st Jan to 31st Jan, and then in the Absences Table there were two entries for that particular Employee, and the date ranges were the 7th to 8th Jan, and 29th Jan to 5th Feb, then the dates to return in that array would be 7th Jan, 8th Jan, 29th Jan, 30th Jan and 31st Jan).
• This then needs to look at this array, and omit from the array any Saturdays and Sundays (I imagine using NETWORKDAYS or similar).
• This then needs to look at the dates in the Public Holidays Table, and omit these dates from the array where these are found.

The formula then needs to return a sum / count of the dates that remain in the array. This should assume that each date has the value 1.

However, if a particular date in the Absences Table has a value in the Duration column of less than 1, then that date is assigned that value when summing the total of the dates, instead of 1.

So if the array of dates was just the 16th and the 25th Jan, but for the 16th Jan the duration stated 0.5, the figure returned in the Formula Column for this Employee would be 1.5.

Hopefully this makes sense. I've attached a simplified version of this workbook, which should contain all of the relevant fields.

Thanks in advance.

Thanks Ali. Yes, fair point. Attached again with all numbers populated manually.

Cheers

Thanks Ali. Not a problem, and appreciate that.

Noted on the lengthy descriptions. Was thinking through the steps in my head and pretty much just wrote down what I was thinking; however appreciate that this could perhaps have been a little more concise.

Have a nice day.

Cheers

Ah, okay. Well then if I'm totally honest, I don't. Apologies. Aside from setting out in simple terms what I want this to achieve, I'm not sure how else I could provide a 'worked example'? Do you mean something like the following. If we look only at the data for say Ian Marshall from the data set.

There are 6x rows of data for Ian Marshall in the Authorised Absences table. The first records an absence from the 24th Dec to 2nd Jan. As the range that we are looking at however (as set out in the Attendances table) is from 1st Jan to 31st Jan, and as the 1st Jan was a public holiday, then there would only be 1x day of authorised absence for that line.

For the next two lines, the range is just a single date; however the duration column shows 0.5 in both, and therefore these were just half days leave (and should just be counted as 0.5 days each).

The subsequent two lines are each for 1x full day leave / absence.

And then the final day is for the date range 29th Jan - 10th Feb. As the range we are looking at however is only for Jan, then this would be 3x days in total for 29th, 30th and 31st Jan.

In total therefore, the number of days absences during the defined range for that employee, and what should be returned by the formula, should be 7 (which is the total of the above).

Note that for this employee, none of the dates referenced fell on a Saturday or a Sunday; however if they did, they would not be counted / included.

If this still isn't what you meant, do let me know, and I'll see how else I can provide the information that you think would make this easier to consider.

Thanks

Thanks Ali.

A full day of playing around with this sadly hasn't resulted in a positive outcome, so I'll see if I can provide the steps you've mentioned above for how I arrived at the manual numbers.

1. I counted the number of days in the Request date range for each employee, where these fell within the defined date range (as specified in the Attendances tab, which is basically the whole of Jan 2024). I then subtracted any dates that fell on a weekend, or were a public holiday. As below screenshot.

Attachment 871093

2. The sum also took into account whether a duration was specified (which are only shown if these are for a period of less than one whole day).

3. The total sum for each employee, I then carried over to the Attendances tab. And that was it.


In trying to find a solution for this, I had thought about identifying the dates from the defined range on the Attendances tab, and omitting the weekends and public holidays from this, to create an array of working days only over the defined range, which I was able to do. This looked like the following:

=LET(
  dateRng, SEQUENCE(C5-B5+1,1,B5),
  weekDayRng, dateRng - 1,
  filteredWeekdays, FILTER(dateRng, WEEKDAY(weekDayRng)<=5),
  excludedPHs, Tbl_PHs[Date],
  FILTER(filteredWeekdays, NOT(ISNUMBER(MATCH(filteredWeekdays, excludedPHs, 0)))))

I then tried to get an array of all dates for a particular employee from the Absences table, that I could then use to look up against the previous data set, and count where there was an overlap of dates / matching dates. I got as far as the following, but this returned a #CODE! error. I think maybe it didn't like the SEQUENCE function being nested within the LAMDA.

=LET(
    absTbl, Tbl_Abs,
    empRng, Tbl_Abs[Full Name],
    empName, "Ian Marshall",
    fromDates, FILTER(INDEX(absTbl, , 3), empRng=empName),
    toDates, FILTER(INDEX(absTbl, , 4), empRng=empName),
    allDates, MAP(fromDates, toDates, LAMBDA(f,t, SEQUENCE(t - f + 1, 1, f))),
    VSTACK(allDates)
)

I was then planning to apply something along the lines of =SUM(COUNTIF(array 1, array 2)).

All of this I would need to incorporate into a single formula, and also I still needed to figure out how this would capture where there were absences for part days only, which this wouldn't at present.

I imagine there is probably a better way of doing this, and I may potentially just be confusing things by providing my initial thoughts on this, but thought I would share anyway, just in case this helped at all.

Thanks again.

Skins