Formula to show how many tasks are occuring during a time frame.

Marc Carden

I have a list of about 60 projects with start dates and end dates. Project name is in the primary column then start date and end date are the other two columns. I want to see how many projects are occurring during each month. This is the formula I have so far but I know it isn't right. I want to have a formula that shows all the projects occurring in Jan, then I will copy for the rest of the months.

Thanks for any help you can provide!!!!!

=COUNTIFS([Start Date-soonest]:[Start Date-soonest], <=DATE(2021, 1, 1), [End date]:[End date], >=DATE(2021, 1, 31))

Kelly Moore

Hey @Marc C

Sorry, for the delay. Don't you hate when work gets in the way of smartsheet 😉

If you're still looking for a solution, I had a thought. I haven't had a chance to test it.

Use two COUNTIFS and add them together.

=COUNTIFS(COUNTIFS([Start Date-soonest]:[Start Date-soonest], >=DATE(2021, 1, 1))+COUNTIFS([End date]:[End date], <=DATE(2021, 1, 31),[Status]:[Status], <>"Complete")

Something like this. I think you need some filter on your End Date that filters out completed projects - so whatever that column is named.

Kelly

Kelly Moore

Hi @Marc C

Only the smallest tweak of your formula is needed. Reverse both your greater than/less than signs.

=COUNTIFS([Start Date-soonest]:[Start Date-soonest], >=DATE(2021, 1, 1), [End date]:[End date], <=DATE(2021, 1, 31))

Marc Carden

Hi KDM, thank you so much for trying to help, I appreciate it very much!

When I use this formula I don't get the right number because I believe this only shows me projects that start and stop in January. Most projects are many months long. Any tweaks to make it so that it just shows any projects that are occurring in Jan, then any projects that are occurring in Feb, which would include most of the projects that started in Jan, etc.

Thanks again, sorry if I wasn't clear enough in my question.

Marc

Kelly Moore

Hey @Marc C

Sorry, for the delay. Don't you hate when work gets in the way of smartsheet 😉

If you're still looking for a solution, I had a thought. I haven't had a chance to test it.

Use two COUNTIFS and add them together.

=COUNTIFS(COUNTIFS([Start Date-soonest]:[Start Date-soonest], >=DATE(2021, 1, 1))+COUNTIFS([End date]:[End date], <=DATE(2021, 1, 31),[Status]:[Status], <>"Complete")

Something like this. I think you need some filter on your End Date that filters out completed projects - so whatever that column is named.

Kelly

Marc Carden

Thank you for your help! I was able to get this working correctly