SUMIFS referencing another sheet with dates

simonvodo

Hello, I am creating a Vacation tracker and trying to use a sumifs formula to calculate the number of days that a person has taken in a given year. The complexity is that the leave anniversaries on the start date of the employee rather than the start of the year. What I would like to do in the Vacation Days used in 2023 column is

sumif(Employee Name = Employee, Days Out, if the Vacation end date is within the last year of the anniversary date).

Sheet 1 - Leave Requests has the below columns

Employee / Supervisor / Type of Leave / Vacation Start / Vacation End / Days Out / Approved

Sheet 2 - Employee Information has the below columns

Employee / Supervisor / Date of Hire / Anniversary Date (2024) / Vacation Days Used in 2023 / Vacation Days Used in 2024 / Future Vacation Scheduled

Currently I am here but cant figure out adding the date part:

=SUMIFS({Leave Requests Days Out}, {Vacation Day Requests Employee Name}, [Employee Name]@row, {Leave Requests Approval}, "Approved")

Does anyone have any advice? Thank you all!

Scott Orsey

Hi @simonvodo , If I understand your question, then I believe you will want to add two conditions. The first to ensure the date is greater than one year prior to the anniversary date and the second to ensure that it is less than the anniversary date. It could look like:

=SUMIFS({Leave Requests Days Out}, {Vacation Day Requests Employee Name}, [Employee Name]@row, {Leave Requests Approval}, "Approved",{Leave Requests Vacation End},@cell>[Anniversary Date]@row-365, {Leave Requests Vacation End},@cell<=[Anniversary Date]@row)

While not in your question, I'm curious how you would treat a vacation that spans two years. Using the above approach, the entire vacation would be counted in the year in which it ends.

Hope this helps. Be well!

simonvodo

@Scott Orsey ! This is awesome! It totally worked. Thank you so much!

Your question back to me is a great one and one that I had considered but thought I needed to get to this point first. Do you have any suggestions?

I super appreciate your help on this, I was stumped!

Scott Orsey

Hi @simonvodo , My suggestion would be to be precise in your vacation policy. There are so many possibilities. For example, what happens if someone doesn't use all of their vacation in a specific year? Do they lose it or does some or all of it carry over? Does the vacation day apply to the appropriate anniversary year regardless of start and end date of the vacation? If so, then you'll need to calculate which portion of the vacation occurs in each year. So, I recommend starting with the policy and moving out from there. Among the things that employees pay close attention to, vacation days ranks right up there and you won't want folks second guessing your calculations.

An alternative would be to use SS only for the request and approval portion of this without trying to calculate the vacation days remaining at a given time. Whomever is doing the review/approval process could look in your HR system before approving to make sure that the employee has sufficient days. Then you wouldn't need to do this calculation at all.

Be well!