SumIfs Date Range

dhamric50866

Hi, 

I'm looking for help writing a SumIfs formula with one of the criterias being a date range.

I'm creating a Time Off Tracking form  to track how much PTO an employee has taken. The tricky part is that we track time off based on date of hire (not the calendar year) which resets at their hire anniversary. This is different for every employee so I'm trying to create a Sum If formula to calculate if an employee has taken time off within their Time Off Period Start and End Date (which I have created separate columns for.)

For calculating Total Sick Days Taken the formula I'm using is:

 =SUMIFS({Total Days Off Requesting}, {Employee}, Employee1, {Type Requested}, ="Sick")

This formula is working great but now I need to add a criteria if the day(s) off requested fall between their Time Off Period Start/End Date. Is this possible?

The sheets I'm using are:

• Employee Time Off Tracker: Tracks how much paid time off an employee has based on their employee status, their Time Off Period Start and End Date, how many days off they've taken off.
• Employee Time Off Request Form (REFERENCE SHEET)  - submitted by employees via web form and includes Employee Name, Time Off Date, Days Requested, Time Off Type (Vacation, PTO etc)

 

Paul Newcome

You would just have to add some additional criteria to your statement. Choose whether you want to base it off of the start or end date of the time off. First criteria would be > Period Start Date, and second addition would be < Period End Date.

daisyeady

Hi @dhamric50866 -- did you ever find a solution for your ask above? I am working on something very similar and am struggling with the "reset" portion as well, resetting the vacation days every anniversary.