Vacation Pro-ration Formulas to encompass Leave time off

Hello, I am looking for some help on formulas that will calculate how many days someone has been out on a specific leave and this time it is within a specified window of time. Refer to this post which I received a solution for earlier:

https://www.excelforum.com/excel-for...e-entries.html

What I'm looking for now basically expands upon that thread. Here goes...

So what the previous thread does is it conditionally formats a person's name to light up if they have exceeded a certain amount of days being out on either one or another type of leave of absence. The two leave types are A&S and WC. If they are on A&S, the trigger to light the cell up is if they are out on leave 90 days or more. If it is WC, the trigger to light up is 6 months or more. The sheet takes into account people going on multiple leaves in a year and so is actually looking for the combined total number days they were out among several leaves of absence.

What I am now looking to do is see how much of that time taken off falls within a specified window of time. On the sheet I have attached, that window of time would be the date range found in Columns D and E, the vacation date and the vacation period end date. As information, Column L has the date the employee actually started their leave of absence while column M has when they returned to work. So basically, I probably need to add an additional column called Days off within pro-rated window. We can just designate column T for that now and it can be moved later as we need. (Bear with me as we are still kind of building/finalizing this file to determine exactly what columns we want, where we want them, etc.) So again, if I can know the amount of days absent within D and E range and have that number populate in Column T.

After that, the next step is in column F, the Pro-rated 2022 column. It should say "Yes" if column K is A&S and column T is greater than or equal to 90 days OR if column K is WC and column T is greater than or equal to 6 months. If none of those conditions are met, it can simply say "No". Bear in mind that as with the previous post referenced, It should take into account employees going on multiple leaves during that window of pro-ration and therefore adding those leaves together. So if someone has a pro-ration period of March of this year to March of next year, but they go out on an A&S leave say in in January of this year, it should only count what is taken after that March window. Let's say their first leave taken in January goes well after March and actually totals 50 days in that March period. 50 days is fine, no pro-ration needed. But lets say then that they go on another leave after that and that one happens to total 45 days. Well now, that leave is purely within that March window unlike the first so now we do total 95 days and in that case, column F should say "Yes"

I hope I explained this clear enough as it was a mouthful. Please let me know though if something need clarified and I will do my best. Thank you so much in advance for any help with this!

Using column H (Days Absent)

in F2

=IF(OR(AND([@[Reason for Leave]]="A&S",[@[Days Absent]]>=90),AND([@[Reason for Leave]]="WC",[@[Days Absent]]>=180)),"Yes","No")

Refer to this post which I received a solution for earlier:

Please don't expect members to cross reference other threads. Please include all relevant detail in this thread. Thanks.