Anniversary Start date to reset Employees Sick leave and Vacation Time

Hello All,

Disclaimer, I am a noob at excel so I hope I can describe things clearly.
I am working off of this Employee Attendance template that I searched for in the excel program. I was able to enter in functions to pull up the employees sick days and vacation time(K3 and R3). I am running into an issue where the amounts reset at the beginning of each calendar year, when I want it to reset at the anniversary date for the employee. And I am just stuck. What my ideal situation would be is that K3 and R3 are able to calculate remaining days based off of the anniversary date. I would also love to be able to roll over any unused vacation time into the next year. The sick days do not roll over and would reset to 3 on the anniversary date. I really like everything else about this template, and the person that would be in charge of inputting information has far less experience with excel than I do, so I am trying to streamline everything for that individual. On the "List of Employees" sheet I have their vacation days as their totals(including rollovers), but I think eventually that would be just their base days, and the rollover would happen through the functions instead of being done manually. That way as employees gain more total vacation days(from more years at the company) that can be changed in that sheet.

Thank you all for any help in advance, it's greatly appreciated!

Whoops! Just cementing my status as excel noob. Thanks for the correction, profile is updated.

Thanks for the reply.

I changed this =IF(DATE(YEAR(TODAY()),MONTH(E4),DAY(E4))<=TODAY(),DATE(YEAR(TODAY()),MONTH(E4),DAY(E4)),DATE(YEAR(TODAY())-1,MONTH(E4),DAY(E4)))
To: =IF(DATE(YEAR(TODAY()),MONTH(E4),DAY(E4))<=TODAY(),DATE(YEAR(TODAY())+1,MONTH(E4),DAY(E4)),DATE(YEAR(TODAY()),MONTH(E4),DAY(E4)))
To get the correct anniversary date Year.
I also changed =Q$2-SUMIFS(LeaveTracker[Days],LeaveTracker[Employee Name],B4,LeaveTracker[Type of Leave],"Sick Leave",LeaveTracker[Start Date],">="&F4)
To: =Q$2-SUMIFS(LeaveTracker[Days],LeaveTracker[Employee Name],B4,LeaveTracker[Type of Leave],"Sick Leave",LeaveTracker[Start Date],"<="&F4)
To give me the correct Sick Days Remaining based off of my first change.
I changed the K3 formula to =((VLOOKUP(valSelEmployee,'List of Employees'!B4:E22,2,FALSE))). And that seems to all be working correctly.

Had a harder time transferring that to the vacation time. I created Base Vacation Times in column P and showed the different days based off of years worked at the company.
I used the same formula with obvious criteria changes to get this: =P$3-SUMIFS(LeaveTracker[Days],LeaveTracker[Employee Name],B4,LeaveTracker[Type of Leave],"Vacation",LeaveTracker[Type of Leave],"Unplanned Vacation",LeaveTracker[Start Date],"<="&F4).
But for some reason that doesn't seem to be working quite right.
I was focusing on Employee 6. It should be showing a -7 for Vacation Time based off of what is in the Leave Tracker sheet.(aka they dipped into their upcoming vacation time) But their anniversary date is for next month. I even tried to take out the P$5 and just run the SUMIFS part of the equation, and it was only giving me back 0, when it should be giving me back 15. I also tried to eliminate this part of the equation LeaveTracker[Start Date],"<="&F4, and again it just gives me 0. My only guess is it has something to do with the Unplanned Vacation and Vacation?

The only other change I made was to the K3 and R3 cells.
K3 had: =((VLOOKUP(valSelEmployee,'List of Employees'!B4:E22,2,FALSE)))-N20
And now it is: =((VLOOKUP(valSelEmployee,'List of Employees'!B4:E22,2,FALSE)))
And that is because the formula you gave me(plus my, hopefully not terrible, changes) now shows the correct days within any calendar year.

Thank you again for your help, this is already leaps and bounds closer to what I am hoping to achieve!

Perfect!
I cannot thank you enough for your help!!

Ok I found another issue im running into.

So I moved employee 6 Start date to 4/18, in the hopes that the vacation time would "reset." Which would mean that the employee would get 15 days again, and then with the -7 would result in 8 days, but it continued to say -7.
=P$5-SUM(SUMIFS(LeaveTracker[Days],LeaveTracker[Employee Name],B9,LeaveTracker[Type of Leave],"Vacation",LeaveTracker[Start Date],"<="&F9),SUMIFS(LeaveTracker[Days],LeaveTracker[Employee Name],B9,LeaveTracker[Type of Leave],"Unplanned Vacation",LeaveTracker[Start Date],"<="&F9))
I think this is because of "<="&F9.
I am not worried about leftover(or in this case advancement of) days, I can create a new column (Leftover Vacation) on the employee page and just add it onto the end of the formula, and that value can be entered in manually at each employees own anniversary. But if there is a way to reset their vacation time on their anniversary date that would be awesome.

That works beautifully. Thank you again for your continued help.