Extract data in rows from multiple column with multiple criteria!!! WEEKDAY/ DATE/ MONTH

Dear Excel Group Members....Hello / Bonjour

I am sure this type of QUIZ has already being solved, but putting it here as I couldnt find anything....I am attaching the quiz spreadsheet here....

This is a vacation tracker that we currently manage manually, in this spreadsheet I have columns from A to G that has...
1. Row-1 Week names
2. Row 2 & 3 Week date
3. Row 4 - 7 Emp Badge #

Column J to L is actually in another tab, Column J is prepopulated but it would be good if it can be auto populated...I want to extract data in those columns from Column A to G.

But the TRICK here is how it should extract the data?
1. Emp # 6000 has vacation planned from 1-Jan-2018 to 28-Jan-2018, but it is spread from Column A to D, it should collate the first and last date and extract in Column K & L
2. Emp # 6010, 6008, 6004, & 6006 takes vacation twice a year so the data should be extracted twice as shown from Row J2:L12 and J14:L17

Let me know if I can explain this in a better way or this helps???

Regards

Chintu....

Hi Glenn,

Thanks for the response appreciate it...below is response to your question

1. I am a bit confused.... where do 29-31 Jan show up. As there a M,T,W, I'd expect tehm to be working days...
Ans. I have only provided dummy data to kind of understand the pattern from week 1 and in actual spreadsheet it goes until Week 52 which is Dec 31...so columns A to G data is in one tab and Column J to L is another tab in which I want to extract the data....



2. Can we see more columns, to the right of column G... to see what patterns exist within the data?
Ans. As answered above the weeks go from Week-1 to Week-52 but I just provided dummy data until Week 9 only...

Let me know if I clarified your question...and thank you for helping me out....

Thanks Glenn, appreciate your help very much. This spreadsheet works for me.

Also one more thing, is it possible that I dont have to prepopulate the rows from L13:M24, it should automatically get added if the Employee is willing to take vacation in say May month?

Thanks

Regards

Chintu...

Thanks this is really helpful....appreciate your time and effort.

Regards

Chintu

Hi Glenn,

There is a problem when I use the formula to extract the start and end date of the vacation for an Emp...the formula doesnt work when there is a situation where the vacation period is end of the current month to beginning of the next month, because may be we are referencing it to the static month or i am not sure...

I have attached example spreadsheet that I actually use, 1st Tab is where I want to extract the vacation start and end dates and Tab 2 has the raw data...
If you look at the example of Emp # 6010, 6029, etc highlighted in Yellow in Tab1, their data is not extracting properly, but for others it is extracting ok....
The only change I made in your formula to extract date is that I created a drop down in Tab1 to filter the month and reference your formula to that cell as absolute reference,....

Let me know if you need additional information

Regards

Chintu

Thanks Glenn, this works like a charm, appreciate your help