Looking to expand tool to track vacation pro-rations

[bh24524]

Hello, so I am helping HR out to make a more efficient tool to track when someone needs to have their Vacation allotments pro-rated due to being out on leaves of absence. Here is a picture of what it looks like and a little bit about it:

In this example, John Smith has an anniversary date of 3/26/2017. His pro-ration window therefore runs from 3/26 of one year to 3/25 of the next year. Whatever year it ends (cell M24) is the year that the pro-ration is for. So since this pro-ration has been completed, he is now earning time credit towards 2025. Cells D19 thru M26 do calculations to determine what the pro-ration window is for each month. A pro-ration window won't necessarily be January 1 thru January 31 for example, it could start halfway in the month and then finish in the next month So D19 for example has the formula

=MONTH(H14)&"/"&DAY(H14)&"/"&(F12-1)

for the start of the month and F19 has the formula

=EDATE(D19,1)-1

for the end of that month's pro-ration window, so the first month runs from 3/26 thru 4/25. To the left of each date range there are either Y's or N's which are typed in the cells. This means that they worked enough days or did not work enough days to get credit for that month to be factored in the overall calculation. Those are what I am looking to expand upon

What I'm looking to start doing is pasting data into Sheet2 that shows what he worked from a range of dates and then have it do a count in each pro-ration month to see how many days they have entries that fall in that window for the following occurrences: REG, OT1, SCK, VAC*, PER, BDY. When pasted into sheet2, I've deleted irrelevant data but essential data looks like this:

*Note, I am waiting for clarification if VAC IS counted in this, but I listed it above just in case it is.
Column B is a week ending date and probably not important. Column C is each individual date with that week where something occurred and then Column G would contain any of the codes I listed above as well as unlisted codes.

So going by the codes I've listed above, if we count from his first months window from 3/26 to 4/25, he has 13 entries that satisfy this criteria - 7 Reg and 6 VAC. Therefore, B19 would populate a "Y". This would be the same for all subsequent month windows. I'm open to either a formula or VBA solution, whatever will get the job done. Once I get confirmation on the VAC code, I will of course post that. There might even be a possibility I need to add additional codes to this criteria, just as a heads up if any solution might be able to take that into account. I hope this all made sense as it was a mouthful. Please let me know if any clarification is needed. Thank you!

 

[bh24524]

I believe I ended up finding a solution to this. It is actually a recorded macro/Formula combo, so this should be resolved.

The macro portion filters pasted raw data on sheet3 and copies and pastes it to sheet2, removing any duplicates as well just in case.

The formula portion

=IF(OR(COUNTIFS(Sheet2!$C$2:$C$1000,">="&D19,Sheet2!$C$2:$C$1000,"<="&F19)>10,V9<>""),"Y","N")

looks at the date ranges given in D and F and calculates the dates on Sheet2 falling within those ranges. It will populate a "Y" if more than 10 dates fall within the range OR if column V has some kind of special notation in it in the case of exceptions, otherwise it will populate "N". From there, it does the rest of the calculations as normal which were already on the sheet prior to the post. Off to the side just for info if needed, I have the formula:

=COUNTIFS(Sheet2!$C$2:$C$1000,">="&D19,Sheet2!$C$2:$C$1000,"<="&F19)

to give an exact count of dates that fall within the specified ranges. Sorry if anyone wasted time on this, but I found something that worked.