Date range calculation, count month numbers

LisaB:-)

Hi All

Could I please ask for some help with a date formula?

I'm working on an online education project. Each module takes around 6+ months to complete, see below the date range that starts on 14 July 2020 and ends on 25 Jan 2021.

I need to count how many modules occur in January, how many in February, how many in March, etc (by year too). So just using the one row of the example below, would result in:

July 2020 = 1, August 2020 = 1, September 2020 = 1, October 2020= 1, November 2020 = 1, December 2020 = 1, January 2021 = 1.

The end purpose is to help resource the project, so if we can tell that we have 9 modules in March 2021, then we will need 9 producers. Any help would be greatly appreciated!

Genevieve P.

Hi @LisaB:-)

Thanks for sharing your sheet, this helps a lot!

Ok, so from here I can see that the issue is we didn't account for a date span that goes from 2020 TO 2021.... so your formula is finding 0 because the two dates start in 2020.

Now, bear with me, as it will look like a big formula but broken down it's the same as what we've already built.

Essentially, we need to add together two formulas.

1 . One that counts if the Start is in 2021 and Finishes after the month you're looking for in 2021

2 . One that counts if the start is in 2020 and Finishes after the month you're looking for in 2021

To do so, we just need to add a + symbol, then copy/paste the entire formula AGAIN, but change the two criteria for the START column to be like so:

Start:Start, IFERROR(MONTH(@cell), 0) >= 1, Start:Start, IFERROR(YEAR(@cell), 0) = 2020

Example:

=COUNTIFS(first formula) + COUNTIFS(second formula)

Final Formula:

=COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Not Required", [CD Module]:[CD Module], 1, Start:Start, IFERROR(MONTH(@cell), 0) <= 1, Start:Start, IFERROR(YEAR(@cell), 0) = 2021, Finish:Finish, IFERROR(MONTH(@cell), 0) >= 1, 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) >= 2, Start:Start, IFERROR(YEAR(@cell), 0) = 2020, Finish:Finish, IFERROR(MONTH(@cell), 0) >= 1, Finish:Finish, IFERROR(YEAR(@cell), 0) = 2021)

^Try this?

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?

Genevieve P.

No worries!

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

Genevieve P.

Hi @LisaB:-)

No problem! I can help you with this. The MONTH function will be ideal here, as it associates a number with a month which you can then search for/between. You'll also want to use the YEAR function as well, since you span across both 2020 and 2021.

Let's use July 2020 as the first example, then you can adjust it for each of the months from there. (Note: I'm building this presuming it's a Cross Sheet formula.)

For July 2020, you'll want to search that the START is either before or equal to July. Then you'll need to ALSO make sure that the END is after or equal to July.

ex - MONTH(@cell) >= 7

But you'll also need to check that the year is equal to 2020

ex - YEAR(@cell) = 2020

Finally, with both of these functions, it's best practice to wrap an IFERROR statement around each of them in case it finds an error with blank date cells in the column range.

ex - IFERROR(MONTH(@cell), 0) >= 7

Then we can use a COUNTIFS (plural) function to count the rows that meet this criteria. You'll list each date range twice (once for the month and once for the year), and each criteria immediately after the range.

Try this final formula:

=COUNTIFS({START Date Column}, IFERROR(MONTH(@cell), 0) >= 7, {START Date Column}, IFERROR(YEAR(@cell), 0) = 2020, {END Date Column}, IFERROR(MONTH(@cell), 0) <= 7, {END Date Column}, IFERROR(YEAR(@cell), 0) = 2020)

Then to look for August, you would just change the 7 to an 8:

=COUNTIFS({START Date Column}, IFERROR(MONTH(@cell), 0) >= 8, {START Date Column}, IFERROR(YEAR(@cell), 0) = 2020, {END Date Column}, IFERROR(MONTH(@cell), 0) <= 8, {END Date Column}, IFERROR(YEAR(@cell), 0) = 2020)

Does that make sense?

Let me know if this works for you!

Cheers,

Genevieve

LisaB:-)

Hi Genevieve, thanks for stepping in.

I'm afraid that's not working. (It's not a cross-sheet formula.) This is how I've customised the formula to reference my columns:

=COUNTIFS([Start], IFERROR(MONTH(@cell), 0) >= 7, [Start], IFERROR(YEAR(@cell), 0) = 2020, [Finish], IFERROR(MONTH(@cell), 0) <= 7, [Finish], IFERROR(YEAR(@cell), 0) = 2020)

I tried replacing '@cell' with '@row', but it didn't recognise the reference. Should this formula be in a Colum Formula, which I can then total at the bottom of the sheet?

Genevieve P.

Hi @LisaB:-)

If you're referencing columns within the sheet, then you'll need to type them twice with a colon between... like so:

Start:Start and Finish:Finish

It should be @cell, since you're looking in the cells of the entire column, versus @row, which would only look in this current row where the formula is placed.

Try this:

=COUNTIFS(Start:Start, IFERROR(MONTH(@cell), 0) >= 7, Start:Start, IFERROR(YEAR(@cell), 0) = 2020, Finish:Finish, IFERROR(MONTH(@cell), 0) <= 7, Finish:Finish, IFERROR(YEAR(@cell), 0) = 2020)

Here's an article on referencing columns that may help in your future formula building. 🙂

This formula would either be placed in its own column in the sheet or in a Sheet Summary field. Let me know if this structure works for you, now!

Cheers,

LisaB:-)

That works great, thank you! The second part of the problem is that I need to introduce another IF statement.

I have a column called 'CD Module', which is a checkbox. If the box is checked, then I want to count the row, if it is blank, then no count. Would you be so kind as to help me with that?

LisaB:-)

Hi again, I'm trying this formula but with no luck so far:

=COUNTIFS(AND(CD Module:CD Module = 1, (Start:Start, IFERROR(MONTH(@cell), 0) >= 12, Start:Start, IFERROR(YEAR(@cell), 0) = 2020, Finish:Finish, IFERROR(MONTH(@cell), 0) <= 12, Finish:Finish, IFERROR(YEAR(@cell), 0) = 2020))

It would also be great if the formula could exclude results from [Status] "Complete" or "Not Required".

Many thanks.

Genevieve P.

Hi @LisaB:-)

The COUNTIFS already implies AND so you don't need to add it in. Instead, just add it as an extra Range then Criteria.

A COUNTIFS works like this:

=COUNTIFS(Range:Range, Criteria, Range:Range, Criteria, Range:Range, Criteria, ... etc)

So, adding in you checkbox criteria:

[CD Module]:[CD Module] = 1

(note, you have to have [These] around the column name because it contains a space. Only one-word column names without numbers can be referenced [without these])

Formula:

=COUNTIFS([CD Module]:[CD Module] = 1, Start:Start, IFERROR(MONTH(@cell), 0) >= 7, Start:Start, IFERROR(YEAR(@cell), 0) = 2020, Finish:Finish, IFERROR(MONTH(@cell), 0) <= 7, Finish:Finish, IFERROR(YEAR(@cell), 0) = 2020)

If you have other criteria, you can say stuff like is not This Criteria, or use <> (which means "not")

ex.

Status:Status, <> "Complete"

Full Formula:

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

Is there anything else you need added in?

LisaB:-)

Thank you, I really appreciate your prompt support. Unfortunately, this one returns #invalid operator

=COUNTIFS([CD Module]:[CD Module] = 1, Start:Start, IFERROR(MONTH(@cell), 0) >= 7, Start:Start, IFERROR(YEAR(@cell), 0) = 2020, Finish:Finish, IFERROR(MONTH(@cell), 0) <= 7, Finish:Finish, IFERROR(YEAR(@cell), 0) = 2020)

and this one returns #unparseable

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

Genevieve P.

Hi @LisaB:-)

Would it be possible to see a screen capture of your sheet? Column names need to be identical to what's in the formula (ex "CD Module" and "Status") in order for it to be read properly.

I also noticed I put

[CD Module]:[CD Module] = 1

instead of

[CD Module]:[CD Module], 1

(the comma works as an =) My apologies!

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

LisaB:-)

Thank you!

LisaB:-)

LisaB:-)

Wait, I've just adjusted it, replacing the = with a , as you suggest, and something's working! Will post back shortly.

LisaB:-)

Hi again. I've used this formula to identify modules that are in production in January 2021.

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

It returns a 0 result, but it should return 2, see the pink lines in this screenshot.

It should return 2, because there will be 2 modules in progress during January 2021. Hope you can help.

Genevieve P.

Hi @LisaB:-)

Thanks for sharing your sheet, this helps a lot!

Ok, so from here I can see that the issue is we didn't account for a date span that goes from 2020 TO 2021.... so your formula is finding 0 because the two dates start in 2020.

Now, bear with me, as it will look like a big formula but broken down it's the same as what we've already built.

Essentially, we need to add together two formulas.

1 . One that counts if the Start is in 2021 and Finishes after the month you're looking for in 2021

2 . One that counts if the start is in 2020 and Finishes after the month you're looking for in 2021

To do so, we just need to add a + symbol, then copy/paste the entire formula AGAIN, but change the two criteria for the START column to be like so:

Start:Start, IFERROR(MONTH(@cell), 0) >= 1, Start:Start, IFERROR(YEAR(@cell), 0) = 2020

Example:

=COUNTIFS(first formula) + COUNTIFS(second formula)

Final Formula:

=COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Not Required", [CD Module]:[CD Module], 1, Start:Start, IFERROR(MONTH(@cell), 0) <= 1, Start:Start, IFERROR(YEAR(@cell), 0) = 2021, Finish:Finish, IFERROR(MONTH(@cell), 0) >= 1, 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) >= 2, Start:Start, IFERROR(YEAR(@cell), 0) = 2020, Finish:Finish, IFERROR(MONTH(@cell), 0) >= 1, Finish:Finish, IFERROR(YEAR(@cell), 0) = 2021)

^Try this?

LisaB:-)

It's working perfectly thank you so much. It's going to be really useful, I'm so grateful for your help 😀

Genevieve P.

No problem at all!! I'm so glad we could get there in the end.

Cheers!

Genevieve