COUNTIFS with date referencing a date range
Hey all,
I have a time off tracker for our installers and their skillset is either level 1,2,3 (picture 1). I need a way to show who is taking off in reference to the date column by level (picture 2) but not sure how to build this formula with a date range. Also, I have it set where if they request one day off, the "End Time Off" will be blank.(pic 1)
Current formula in "Level 1 off" (picture 2) column is below:
=COUNTIFS({Level}, "1", {start time}, Date@row)
The formula above will only show that one date but not sure how to show if they take off 8 days for example without building out multiple helper columns. Any help would be great!
Best Answer
-
I would suggest populating the End Time column even on those where it is just one day. Then your COUNTIFS would look something like this...
=COUNTIFS({Level}, @cell = 1, {Start Date}, @cell<= Date@row, {End Date}, @cell>= Date@row)
Answers
-
I would suggest populating the End Time column even on those where it is just one day. Then your COUNTIFS would look something like this...
=COUNTIFS({Level}, @cell = 1, {Start Date}, @cell<= Date@row, {End Date}, @cell>= Date@row)
-
@Paul Newcome That worked thank you so much!
-
Happy to help. 👍️
-
@Paul Newcome Can you think of a workaround if I didn't want the formula to count weekends? Just realized it is counting Start to end dates with Saturday included. Maybe wrap it with a NETWORKDAYS formula?
-
You want to exclude weekends from the source sheet or weekends from the formula sheet?
-
@Paul Newcome from the formula sheet. I already have a formula with NETWORKDAYS for the # of days requested on the source sheet. Just need for that COUNTIFS formula to skip Saturdays because when we take off, it's M-F only.
-
@Paul Newcome we still work saturdays, so I still need it to have saturday dates. But when employees request off, it only counts during M-F, basically need the formula to not count Saturdays but still want saturdays to be seen so we can see who is available to work if that makes sense
-
On the first sheet or the second sheet?
-
@Paul Newcome The first sheet is set-up with a NETWORKDAYS formula to track # of days they plan to take off (our company tracks days off M-F). The second sheet has the first date manually put in with all the rest of the dates under it with =WORKDAY(DATE1, 1) to track all working days until the end of the year. I have project settings set up on the second sheet so it counts Saturdays and not Sundays with holidays as well because we work on Saturdays but our vacation days only count for days M-F. With the original COUNTIFS formula, it counts the days from start to finish but I need it to skip Saturdays on the second sheet because it looks like we have no availability that day when in reality we do
-
@Paul Newcome I figured out a workaround formula. I added a column for the weekday and just didn't count days that are Saturdays. Below is the formula I came up with.
=IF([WEEKDAY date]@row = 7, 0, COUNTIFS({Level}, @cell = 1, {start time}, @cell <= Date@row, {end date}, @cell >= Date@row))
Help Article Resources
Categories
Check out the Formula Handbook template!
