CODEWITHSUNDEEP
excel
bezj
bezj

Reputation: 93

Excel Index to check Start Date-End Date and all dates in between

Excel2019 and Excel online

I have a table with approved leave for my team that is formatted like this:

Table name: leave_data
Name | start date | end date | leave type
emp1 | 4/1        |  4/3     | V

...

On a separate worksheet I have dates going horizontally across columns and a table with employee names like this:

month: April

name | 4/1 | 4/2 | 4/3 | ... | 4/30
emp1

and I want the result to be:

name | 4/1 | 4/2 | 4/3 | ... | 4/30
emp1 |   V | V   |  V

I have tried using this formula:

=(INDEX(leave_data[[Leave Type]:[Leave Type]],SUMPRODUCT(--(leave_data[[Name]:[Name]]=emp1),--(leave_data[[Start Date]:[Start Date]]<=B$2),--(leave_data[[End Date]:[End Date]]>=B$2),ROW(leave_data[[Leave Type]:[Leave Type]])-ROW(leave_data[#Headers])))

and it seems to work, but starts behaving strangely once there are numerous entries in the leave_data table. Open to other solutions as well, but ultimately I need a formula I can drag horizontally that will put the leave_data[leave type] entry in all the dates(columns) between leave_data[start date] and leave_data[end date].

Thanks in advance.

Upvotes: 0

Views: 387

Answers (1)

Variatus
Variatus

Reputation: 14373

This is the formula you want. It's designed for cell B12 and can be copied from there both horizontally and vertically. Note that it's an error formula and needs to be confirmed with Ctl+Shift+Enter.

=IFERROR(INDEX(leave_data,MATCH(1,(leave_data[Name]=$A12)*(leave_data[Start Date]<=B$11)*(leave_data[End Date]>=B$11),0),4),"")

[A10 =] Month:
[B10] = 1/4/2020 with cell format `mmmm` (to display "April")
[A11 =] Name
[B11] =B10
[C11] =IF(MONTH(B11+1)=MONTH($B$10),B11+1, "")
[B11:C11] cell format = `dd/mm` to display "01/04"
[C11] - copy across to AF
[A12 =] First employee name: More names below in column A
[B12 =] Above formula. Copy across down as far as you have names in column A
[B12:B?] Copy across to AF

You will have to adjust the rows but that's the setup I tested. The point is that you can't copy an array formula from B12 to B12:AF12 (including its original cell). Therefore you first enter the formula, with Ctl+Shift+Enter, and then first copy the cell down and, in a third step, copy the column across.

Upvotes: 1

Related Questions