Forum Discussion
How to count number of employees days on work between 2 dates (countif etc)
https://1drv.ms/x/s!AgVMgapiFJvPgZAwNWKDix56QnnYSw?e=sDzfak
Ideas?
I forgot one quote, but the formula has to be different since the start and finish values have a time component.
I have updated your workbook.
The counter is working really good.
I would like to improve it...
I dont wanna count the days in the "middle" when 2 persons swap place.
As of now, I I have to se where 2 dates are meeting, and then take -1 on that count.
I have drawn lines where dates meet. On those places I wanna remove 1 from the count.
Analogy.
Imagine these dates represent people on a boat. And my job is to see which people are on "duty" that day. And all date lines are when people are working, and on the last day of work, I don't wanna count them as active if that makes sense. Bc they are leaving that day. So I wanna remove the last day IF that date line meets another date coming out... Always keeping track on people on active duty.
So i was thinking... if some compare function can do that. If comparing 2 dates and in that way remove one from that day.
- basically... remove the last day before doing the count on each "end" finish date should work..
-1 on each end day.. they do the normal formulaHow about
=SUM((INT($B$3:$B$10)<=A15)*(INT($C$3:$C$10)>A15))
for the original data range, and
=SUM((INT($G$4:$G$51<=A15)*(INT($H$4:$H$51)>A15)))
for the one on the right.
Fill down.
- It works perfectly! Thank you so much!