SOLVED

New Contributor

# Count Open Jobs on any particular date

Hello,

I have a large spreadsheet of logged jobs.  Each has an open date and a closed date.  What I'd like to do is show how many jobs were open on any given date - and then ideally create a graph showing the number of open jobs changing over time.  Is this possible?

Thanks

6 Replies

# Re: Count Open Jobs on any particular date

Let's say the Open Dates are in B2:B1000 and the Closed Dates in C2:C1000.

Elsewhere, enter the start date for the chart, then fill down to the end date.

Let's say this date series is in E2:E32.

In F2, enter the formula

=COUNTIFS(\$B\$2:\$B\$1000,"<="&E2,\$C\$2:\$C\$1000,">="&E2)

and fill down.

Use columns E and F for your chart.

See the attached sample workbook.

# Re: Count Open Jobs on any particular date

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

best response confirmed by Matthew830 (New Contributor)
Solution

# Re: 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,"")

# Re: Count Open Jobs on any particular date

Perfect - thank you so much.

# Re: Count Open Jobs on any particular date

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.

# Re: Count Open Jobs on any particular date

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.