If name matches, then find all dates that fall between two dates

tkc123

New Member
Joined
Oct 12, 2018
Messages
6
Hi,
I have a list below to count absentees,

Start Date, End Date, Name, No of Days
11-Nov, 13-Nov, Lee, 3 =IFERROR(MAX(1,IF(A2<>"",NETWORKDAYS.INTL(A2,B2,11,Holidays!$A$2:$A$10),"")),"")
11-Nov, , Ken, 1
15-Nov, 16-Nov, Lee, 2
15-Nov, 17-Nov, Jess, 3

Without listing all dates that fall between the Start and End Dates, what formulas could I use to mark x if a date below falls between the Start and End dates in the table above? (while automatically considers ONLY 1 day if no End Date is entered)

Name, 11-Nov, 12-Nov, 13-Nov,14-Nov, 15-Nov, 16-Nov, 17-Nov
Jess, , , , , x, x,
Lee, x, x, , , x, x,
Ken, x,

Thanks a lot.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
I am having difficulties with integrating comparison (whether the date is bigger or smaller than Start/End Date) onto INDEX-MATCH.

The formula below doesn't find through all names in AbsenteeList (first list).
=IF(ISNUMBER(MATCH(List2[Name],AbsenteeList[Name],0)),IF(AND(List2[Dates]>AbsenteeList[StartDate],List2[Dates]
<absenteelist[enddate]),"x",""),0)[ quote]<="" html=""></absenteelist[enddate]),"x",""),0)[>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top