Calculating Month from Date Column

Andrea Mayer

Hello,

I am looking for help with the formula below. I am referencing another sheet and trying to calculate the number of rows that fall within January in the 'Milestone Due Date' column. The dates in the 'Milestone Due Date' column displayed in the following format: mm/dd/yy.

=COUNTIFS({Project}, [Primary Column]@row, {Milestone Due Date}, =MONTH(1))

Appreciate any help anyone can provide.

Thanks!

Andrea

Brian W

Try this:

=COUNTIFS({Project}, [Primary Column]@row, {Milestone Due Date}, MONTH(@cell)=1)

(Note: I find that COUNTIFS and MONTH only works if you specify a range of cells in column, and not use the whole column.)

Paul Newcome

Brian,

 

In regards to your note, the reason may be because there are blank cells in your column. Try wrapping the MONTH statement in an IFERROR statement. Something along the lines of...

 

IFERROR(MONTH(@cell) = 1, 0)

 

What this will do is if there is an error within any cell the MONTH function is looking at (to include blanks), it will replace that with 0. Because 0 is a number, it will negate the error of a blank cell (or non-date value) and allow your formula to continue working. The other nice thing is that there is no month 0, so you don't have to worry about it inadvertently being included in other calculations.

Andrea Mayer

Thanks Brian. This worked like a charm!

Brian W

Thanks Paul. This is a great workaround. 

Paul Newcome

Happy to help. I learned this one the hard way... Haha

Andrea Mayer

Brian & Paul -

Is there also a way to exclude anything that is not in 2019 in this same formula?

Thanks!

Andrea

Paul Newcome

Yes. We just need to add in a YEAR function...

 

=COUNTIFS({Project}, [Primary Column]@row, {Milestone Due Date}, MONTH(@cell)=1, @{Milestone Due Date}, YEAR(@cell) <> 2019)

Andrea Mayer

Fantastic! Thank you for your help, Paul!

Paul Newcome

No worries. Happy to help!