Pull through selective dates only based on various criteria

[Crackerdaq]

Hi,

I attach an example of what I am trying to do. Have already had some help on this but every time I crack it, another variable seems to get added in!

On the sheet 'Split Year Leave' in L9 downwards, I want to pull in the relevant Bank Holiday dates for the period based on the dates set in C9 and C10. I have already done this but the issue is that some of the dates required don't fall exactly between these dates. For calculation purposes, there is always 8 in one financial year and so some get moved to the one before or after to balance them out. On sheet 'Data' in E2 I have set out in columns exactly what dates fall in what period so this can be seen.

I have added some dates in the sheet to populate it and you will see I need it to also show the BH on the 30/3/18.

On another sheet 'Full year leave' i just reference the column required in B12 as that will only ever show 8. This sheet therefore works perfectly for what I need.

I am guessing I need a formula to go in L9 that is a mixture of the one currently in it and also the one currently in 'Full year leave' B12 to ensure that the right data gets pulled through.

I hope I have explained that properly....Can anyone help please?

Thanks, Crackerdaq

[JohnTopley]

... If I understand the check will be:

If any BH between the Dates specified falls on a Monday, check if previous Friday is BH OR if BH falls on a Friday, check if following Monday is a BH: I either are TRUE, add to list ????

[Crackerdaq]

Hi,

Thanks for reply. I think so but not sure that would only limit to 8 over the years.

I would think the easiest check would be to display the range of BH based on the entry in C11 and then only show the dates that fall between the range in C9:C10. This pulls in from the corresponding columns in the 'data' tab.

Thanks

[shukla.ankur281190]

WD by Ankur

[JohnTopley]

That would not work as it would not select 30/08/2018 which what I Understood you wanted for dates currently shown C9:C10

you will see I need it to also show the BH on the 30/3/18

[Crackerdaq]

Good point....you're right it wouldn't work.

Looking at your check, it wouldn't work in 2024/2025 as would pull through 29/3/24. Similar for the years onwards.

[JohnTopley]

The logic needs to test if the Friday situation exists in the selected year: as the Monday is the first date in 24/25 the previous Friday (in previous year) would (have to) be ignored.

[Crackerdaq]

Ok that makes sense. How though?

[JohnTopley]

The "easiest" way I can think of is to check the date in C9 against the appropriate year i.e tables in E onward: if it (C9) is a Monday, check if date C9-3 [a Friday] in the table, then change C9 to this date.

However to do this will require VBA.

[Crackerdaq]

Not easy then - especially for me as have no idea on VBA (was at the limit of my knowledge with a lot of the above!)

Having looked at it, a workaround for 2018/19 is to use 30/3/18 as a start date in C9 as all the other calculations will work. The issue comes in 2024/25 but perhaps by that time I will either have learnt VBA or more likely I can get someone else to do it!

Thanks for your help though.

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.