Count how many jobs start in a month

11/08/19 Edited 12/09/19

I am trying to create a formula that counts how many jobs are due to start in a month, that havent already been started. I have created the below formula to try and count the jobs in the current month:

=COUNTIFS({Tippa JC Status}, ="", {Tippa Start}, MONTH({Tippa Start}) = MONTH(TODAY()))

 

Then I try and modify it to the following for the following month, and then the month after that:

=COUNTIFS({Tippa JC Status}, ="", {Tippa Start}, MONTH({Tippa Start}) = MONTH(TODAY(30)))

=COUNTIFS({Tippa JC Status}, ="", {Tippa Start}, MONTH({Tippa Start}) = MONTH(TODAY(60)))

However, none of these are working currently - what am i missing out?

NB: the Tippa JC status is looking up to another sheet to check that the completion date is blank. Tippa Start references the start date on the same sheet that the Tippa JC Status pulls from.

 

 

Comments

  • Hi Grant,

    The way you build your formula will depend on the exact criteria you are looking for, or how you define a "month" (if you mean in terms of days or in terms of month name). Based on the formulas you are writing, it seems that you are looking for jobs within the next 30 days, or then within 30 - 60 days, which could be counted by using the following formula structure:

     

    =COUNTIFS({Tippa JC Status}, ="", {Tippa Start}, >=TODAY(), {Tippa Start}, <=TODAY(30))

    This will count anything that has a blank status between Today and Today+30 days.

     

    =COUNTIFS({Tippa JC Status}, ="", {Tippa Start}, >=TODAY(30), {Tippa Start}, <=TODAY(60))

    This will count anything that has a blank status that is between 30 days from now, but less than 60 days from now.

     

    Keep in mind that if you are in the middle of the month (such as now) this will be counting half of November and half of December. Let me know if that's not what you were looking to do; in this case, it may be helpful to see screen captures of your two sheets, but please hide any sensitive data.

    Thanks!

    Genevieve

Sign In or Register to comment.