Calculating Month from Date Column

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

Find more posts tagged with

Hospitality

Formulas

Project Management

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…