Countif a dropdown was selected before a certain date

Kristen Rasmussen

In our project management process, we have a drop down column for an employee to note where a project is in the process, ending with "Completed". I want to do a count for how many of these projects were completed in our last fiscal year. I'm looking for a formula to say "if "completed" was selected before 7/1/2020, then count".... does anyone have any recommendations for how to accomplish that?

Paul Newcome

You would need to have a column that houses the date the row was marked completed. Then you could use a formula such as

=COUNTIFS({Date Range}, IFERROR(@cell, DATE(2020, 07, 02)) <= DATE(2020, 07, 01)

Andrée Starå

Hi @Kristen Rasmussen

I hope you're well and safe!

To add to Paul's excellent advice/answer.

You could use a Workflow with the Record a Date feature to store the date automatically.

Would that work/help?

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Kristen Rasmussen

Thank you both! I've created a date column that uses an automatic workflow and use the formula created by Paul - they work great!

Paul Newcome

Happy to help. 👍️

Kristen Rasmussen

Now I'm looking at setting up a formula to prepare for the upcoming year. Obviously if I do a before function, it will include ALL previous fiscal years. How would I set this up to work for a period of time between July 1 2020 and June 30 2021?

Andrée Starå

@Kristen Rasmussen

Excellent!

You're more than welcome!

Paul Newcome

Your formula would end up looking more like this...

=COUNTIFS({Date Range}, AND(IFERROR(@cell, DATE(2020, 06, 30)) >= DATE(2020, 07, 01), IFERROR(@cell, DATE(2021, 07, 02) < DATE(2021, 07, 01)))

The above is between 1 July 2020 and 30 June 2021.