Creating a Date range

Kimberly Washeleski

I am trying to create a date range and keep getting an error. I would appreciate assistance. Thank you

=COUNTIFS([Business Unit]:[Business Unit], "CAI Business", Status:Status, "In Progress", (AND [Start Date]:[Start Date] >4 / 1 / 21, [Start Date]:[Start Date]<06/30/21))

Paul Newcome

Try somethign like this...

=COUNTIFS([Business Unit]:[Business Unit], "CAI Business", Status:Status, "In Progress", [Start Date]:[Start Date], AND(@cell > DATE(2021, 04, 01), @cell < DATE(2021, 06, 30)))

Kimberly Washeleski

Thank you Paul. This formula worked, but I realized I am not asking enough and am wondering if it would be easier to create a new column for quarters.

I need to have Q1, Q2, Q3, Q4 status to add to a report and dashboard.

I have a start date after 1/1/2021 and an end date before 4/1/2021. However, for Q2, I may have projects that started in Q1, but will continue into Q2. How do I account for those?

Maybe a new column?

Thank you so much

Paul Newcome

I personally would suggest 4 checkbox columns (one for each quarter) that can be hidden after setting them up. You would use an IF formula in each to check the box if the dates overlap the quarter.

For the first quarter you would use:

=IF(AND(MONTH([Start Date]@row)<= 3, MONTH([End Date]@row)>=1), 1)

Then for subsequent quarters you would compare the start month to the last month of the quarter and the end month to the first month in the quarter.

Then you can build your Q1 report based on whether or not the Q1 box is checked, same for the Q2 report having the Q2 box checked, so on and so forth.

Is that what you are looking for?