Total time-off days by year

SylviaL

I am having trouble creating a formula on my Staff Total Days Off sheet that pulls information from my Time-off Request Log sheet but for a specific year.

Sheet 1: Time-off request sheet tracks each employee's approved time-off dates and days with an additional column for YEAR of request.

Sheet 2: The Total Days Off sheet sums up all of the time-off days used by employee pulling data from Sheet 1. I have successfully created the formula that adds all of the time-off days used per staff.

Total Days Used formula: =SUMIF({EMP ID}, [EMP ID]1, {Days Out})

However, it is adding all days off for all of the YEARS (eg: 2022, 2023 etc) for each employee. How do you write a formula that Totals Days Used by employee but only for a specific year eg: 2023?

Thanks,

Sylvia

Jaime M.

Hi Sylvia,

If you set up Sheet 1 like in the first snip, with a column for time off start and end, you can have a helper column that sums the total days off per request. I used the Networkdays formula, but didn't include a reference list of holidays (which I would suggest you use to make the count accurate, but make in a separate reference sheet) - this is shown in the second snip below.

=NETWORKDAYS([Time Off Start]@row, [Time Off End]@row)

Then, in your Sheet 2, you can reference Sheet 1 using the formula in the last snip below.

=SUMIFS({Count If - Days Off}, {Count If - Employee ID}, [Employee ID]@row, {Count If - Year}, "2023")

Hope this helps!

SylviaL

Thanks Jamie!

It didn't work though. I set up the Time off sheet as you said. Then entered the formula in Sheet 2:

=SUMIFS(COUNTIF({Days Out}, COUNTIF({EMP ID}, [EMP ID]@row, COUNTIF({Year}, "2023"))))

I got an Incorrect Argument Set on the cell.

Any ideas on what I did wrong?

Erik Jensen

Hi Sylvia,

Did you get this figured out? If not, I might be able to help.

SylviaL

Yes I did. Many thanks for reaching out.