Forum Discussion
Count Open Jobs on any particular date
You could use
=COUNTIFS($B$2:$B$1000,"<="&E2,$C$2:$C$1000,">="&E2)+COUNTIFS($B$2:$B$1000,"<="&E2,$C$2:$C$1000,"")
Thanks so much for sending this - it work brilliantly. However I don't think it has an allowance for jobs that are still open. i.e. my data set has over 1000 open jobs which don't have an a closed date - however these are recorded as being open. I assume that this is because they need to have a closed date to be counted. Is there anyway to include these still open jobs?
thanks
You could use
=COUNTIFS($B$2:$B$1000,"<="&E2,$C$2:$C$1000,">="&E2)+COUNTIFS($B$2:$B$1000,"<="&E2,$C$2:$C$1000,"")
- Perfect - thank you so much.
This great. Will it also work down to the minute. My jobs last between 1 to 3 hours. So I want to know how many jobs are opened every hour.
Using the same example as in my screenshot above: I assume that columns B and C contain dates+times, or perhaps only times.
Column E should contain a list of dates/times or times by the hour: 0:00, 1:00, 2:00 etc.
The same formulas should then work for you.
