How to Count Vacation days

Problem - Counting Vacation within a Specified Time Period

Below in column A there are dates when an employee was absent.

Letter V in column B indicates Vacation, PH – Public Holiday, T - Travel.

I want to count the number of vacation days during the last 180 days.

Solution

We should use SUMPRODUCT function in the following formula:

=SUMPRODUCT(((TODAY()-A2:A23)<=180)*(B2:B23="V"))

Explanation

TODAY function returns today’s date.

The expression [TODAY()-A2:A23] returns an Array consisting of the difference between today’s date and each date in column A (cells A2:A23), each value (the number of days between today’s date and a particular date in column A) in that Array, representing, is compared with 180 and returns "1" (TRUE) for every number in the Array that is smaller than or equal to 180, and "0" (FALSE) for every number that isn’t.

The expression [B2:B23=”V”] returns an additional Array consisting of "1" (TRUE) for every value in column B (cells B2:B23) that matches the absence type ("V") and "0" (FALSE) for every value that doesn’t:

The result represents the number of vacation days during the last 180 days.