wknight1983
New Member
- Joined
- Jan 2, 2017
- Messages
- 2
Hey guys i have been attempting to do a look up or match to get date ranges from a list of peoples holidays, into a calendar to offer a printable calendar to see what dates are booked already and what can still be booked..
so on Tab 1
[Leave]
<tbody>
</tbody>
<tbody>
</tbody>
I would like to match these date ranges and return the name and type of holiday importantly picking up each variant covering a specific date. My second tab covers a month (Jan 1st - 31st) in b-d Feb (1st -31st) in e-g until June then the following months are repeated in the same format below.
[Calendar]
<tbody>
</tbody>
I have this formula that works but its not ideal very resource heavy and slows excel to much. Anyone out there fancy a challenge to see if they can come up with a slicker formula?
Kind Regards
Wayne Knight
so on Tab 1
[Leave]
Name (A1) | Start Date | Finish Date | Type |
Webb, Lesley | 09/01/2017 | 13/01/2017 | Training |
Pernal, Izabela | 09/01/2017 | 13/01/2017 | Training |
Knight, Wayne | 12/01/2017 | 29/01/2017 | Holiday (Paid) |
Gronow, Frances | 15/01/2017 | 15/01/2017 | Holiday (Paid) |
<tbody>
</tbody>
<tbody>
</tbody>
I would like to match these date ranges and return the name and type of holiday importantly picking up each variant covering a specific date. My second tab covers a month (Jan 1st - 31st) in b-d Feb (1st -31st) in e-g until June then the following months are repeated in the same format below.
[Calendar]
Date(B3) | Weekday | List of Returned values |
08/01/2017 | Sun | - |
09/01/2017 | Mon | Webb, Lesley - (Training) Pernal, Izabela - (Training) |
10/01/2017 | Tue | Webb, Lesley - (Training) Pernal, Izabela - (Training) |
11/01/2017 | Wed | Webb, Lesley - (Training) Pernal, Izabela - (Training) |
12/01/2017 | Thu | Webb, Lesley - (Training) Pernal, Izabela - (Training) Knight, Wayne - (Holiday (Paid)) |
13/01/2017 | Fri | Webb, Lesley - (Training) Pernal, Izabela - (Training) Knight, Wayne - (Holiday (Paid)) |
14/01/2017 | Sat | Knight, Wayne - (Holiday (Paid)) |
15/01/2017 | Sun | Knight, Wayne - (Holiday (Paid)) Ostrowska, Katazyna - (Training) |
16/01/2017 | Mon | Knight, Wayne - (Holiday (Paid)) |
Ect... | Ect... | {See formula below} |
<tbody>
</tbody>
I have this formula that works but its not ideal very resource heavy and slows excel to much. Anyone out there fancy a challenge to see if they can come up with a slicker formula?
Code:
[FONT=Lucida Grande]=IF([/FONT][COLOR=#1A5AD3][FONT=Lucida Grande]C3[/FONT][/COLOR][FONT=Lucida Grande]="","",IFERROR[/FONT][COLOR=#006110][FONT=Lucida Grande]([/FONT][/COLOR][FONT=Lucida Grande]LOOKUP[/FONT][COLOR=#AB37D3][FONT=Lucida Grande]([/FONT][/COLOR][FONT=Lucida Grande]2,1/[/FONT][COLOR=#A2492D][FONT=Lucida Grande]([/FONT][/COLOR][FONT=Lucida Grande]Leave!$F:$F<=[/FONT][COLOR=#006110][FONT=Lucida Grande]B3[/FONT][/COLOR][COLOR=#A2492D][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande]/[/FONT][COLOR=#A2492D][FONT=Lucida Grande]([/FONT][/COLOR][FONT=Lucida Grande]Leave!$G:$G>=[/FONT][COLOR=#006110][FONT=Lucida Grande]B3[/FONT][/COLOR][COLOR=#A2492D][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande],Leave!$A:$A&" - ("&Leave!$D:$D&")"[/FONT][COLOR=#AB37D3][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande],"-"[/FONT][COLOR=#006110][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande]&IFERROR[/FONT][COLOR=#006110][FONT=Lucida Grande]([/FONT][/COLOR][FONT=Lucida Grande]"[/FONT][FONT=Lucida Grande]"&LOOKUP[COLOR=#ab37d3]([/COLOR]2,1/[COLOR=#a2492d]([/COLOR]Leave!$H:$H<=[COLOR=#006110]B3[/COLOR][COLOR=#a2492d])[/COLOR]/[COLOR=#a2492d]([/COLOR]Leave!$I:$I>=[COLOR=#006110]B3[/COLOR][COLOR=#a2492d])[/COLOR],Leave!$A:$A&" - ("&Leave!$D:$D&")"[COLOR=#ab37d3])[/COLOR],"-"[COLOR=#006110])[/COLOR]&IFERROR[COLOR=#006110]([/COLOR]"[/FONT]
[FONT=Lucida Grande]"&LOOKUP[/FONT][COLOR=#AB37D3][FONT=Lucida Grande]([/FONT][/COLOR][FONT=Lucida Grande]2,1/[/FONT][COLOR=#A2492D][FONT=Lucida Grande]([/FONT][/COLOR][FONT=Lucida Grande]Leave!$J:$J<=[/FONT][COLOR=#006110][FONT=Lucida Grande]B3[/FONT][/COLOR][COLOR=#A2492D][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande]/[/FONT][COLOR=#A2492D][FONT=Lucida Grande]([/FONT][/COLOR][FONT=Lucida Grande]Leave!$K:$K>=[/FONT][COLOR=#006110][FONT=Lucida Grande]B3[/FONT][/COLOR][COLOR=#A2492D][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande],Leave!$A:$A&" - ("&Leave!$D:$D&")"[/FONT][COLOR=#AB37D3][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande],"-"[/FONT][COLOR=#006110][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande])[/FONT]
Kind Regards
Wayne Knight