Countif comparing months in a date

s_mawer

Hi

I'm trying to figure out a countif formula to count up the number of times dates in 2 columns are not in the same month but struggling to figure it out.  Can anyone help?

I've attached a screen shot to show bit more clearly what I mean.  Basically I want a count to show the number of times things were not prepared and submitted in the same month - so that I can figure out how many times prep spanned over one month in to another.  Is this possible?  Note, my dates are in the UK format of DD/MM/YYYY.

Thanks

 

Paul Newcome

The easiest way would be to create a helper column. For this example I will use a Flag/Symbol type column to flag the row if the months for the two dates do not match.

 

=IF(MONTH([In Prep]@row) <> MONTH(Submitted@row), 1)

 

This will flag the rows where the months don't match, and then you can use a basic COUNTIFS on the helper column to count how many rows are flagged.

 

=COUNTIFS(Helper:Helper, 1)