Date range calculation, count month numbers

LisaB:-)

I've hit a problem, in that the modules that fall into 2022 aren't being counted. The calculation is returning 5 modules for November 2021, but there are in fact 15 modules.

How can we solve this? Would a 'third' calculation need to be added to the 'two' there already? Many thanks. @Genevieve P

Genevieve P.

Hi @LisaB:-)

Yes, you could add in a third statement to look for 2022, but specifying that the ending might be in 2022.

So for June 2021 where you may have some starting in 2020 or some other rows ending in 2022, then you could do something like so:

=COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Not Required", [CD Module]:[CD Module], 1, Start:Start, IFERROR(MONTH(@cell), 0) <= 6, Start:Start, IFERROR(YEAR(@cell), 0) = 2021, Finish:Finish, IFERROR(MONTH(@cell), 0) >= 6, Finish:Finish, IFERROR(YEAR(@cell), 0) = 2021)

+

COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Not Required", [CD Module]:[CD Module], 1, Start:Start, IFERROR(MONTH(@cell), 0) >= 6, Start:Start, IFERROR(YEAR(@cell), 0) = 2020, Finish:Finish, IFERROR(MONTH(@cell), 0) >= 6, Finish:Finish, IFERROR(YEAR(@cell), 0) = 2021)

+

COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Not Required", [CD Module]:[CD Module], 1, Start:Start, IFERROR(MONTH(@cell), 0) >= 6, Start:Start, IFERROR(YEAR(@cell), 0) = 2021, Finish:Finish, IFERROR(MONTH(@cell), 0) >= 6, Finish:Finish, IFERROR(YEAR(@cell), 0) = 2022)

Since your rows seem to only span around 6 months or so, it sounds like once you get to October 2021 then you won't need the first COUNTIF part of the formula, since nothing will be starting in 2020 and still going on through October. Does that make sense?

LisaB:-)

Hi @Genevieve P

I'm afraid that's not working. For February 2021, it's returning a total of 10, when a manual count reveals a total of 8.

I'm stumped.

Genevieve P.

Hi @LisaB:-)

Hmm, it works on my sheet for February 2021. Would it be possible to see a screen capture of the 8 February rows?

I've thought of a different way of doing this which would eliminate the need to specify the years and duplicate the formula using + signs, etc.

Instead of using MONTH and YEAR, we could use the DATE function to plug in a specific date to search for... so for February it would look like this:

Start:Start, @cell <= DATE(2021, 2, 28), Finish:Finish, @cell >= DATE(2021, 2, 1)

Then we don't need all those IFERRORS and MONTHS and YEARS, etc.

Cleaner formula:

=COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Not Required", [CD Module]:[CD Module], 1, Start:Start, @cell <= DATE(2021, 2, 28), Finish:Finish, @cell >= DATE(2021, 2, 1))

You'd need to adjust the DATE(in here) for each month, starting with the last day of the Month as the first criteria, and the first day of the month as the second. Does that make sense? Does this give you the correct row numbers?

LisaB:-)

Thanks @Genevieve P I have found a solution by downloading it to excel and manipulating the data there, it doesn't provide a 'live' report but it will meet my current deadline. I'm going to try your new v2. formula over the next couple of days, thank you for sticking with it.

Genevieve P.

No worries!

I think the second formula will actually be much faster/better, I should have thought of it from the beginning!

LisaB:-)

That's got it! Thank you - that's a much better formula, much easier to keep updated. Thanks again 😍