Formula to check if the month and year of 2 cells are the same except for certain circumstances

[Charlie_Sondag]

I have a formula to check if the month and year of 2 cells are the same with a result of True or False, however, I need to add another step to the formula to return True even if the mm/yy aren't the same if another column has a specific statement.

Column A = Description

Column B = Date 1

Column C = Date 2

My current formula is: =Month(B1)&Year(B1)=Month(C1)&Year(C1)

The description column may say Shares Issued, Distribution Payment or Shares Redeemed

[#2 · Accepted solution]

Hi Charlie,

You should use a simple nested IF here. Rather than do =Month&Year=Month&Year to get your true/false, start from an IF statement.
=IF(Month(B1)&Year(B1)=Month(C1)&Year(C1),True,False)
This will get your match on month/year like you currently do, and makes expanding and doing other things much easier. So what we're going to do now is check for the specific statement, before checking your dates. For example if you want to check for "Shares Issued" in column A, and return true regardless of dates, but then still return true for any dates that are in fact matching regardless of description, you could say:

IF(A1="Shared Issued",True,IF(Month(B1)&Year(B1)=Month(C1)&Year(C1),True,False))

Now I'm the type of person that likes to have informative statements instead of True/False. I also don't like seeing 10 lines of "FALSE" in between my true records, I'd rather the falses show up blank so I can more quickly identify my true statements. The human eye can find all the true text when there's no blanks significantly faster. So replace your true and false returns with what you want it to say instead. Example:

IF(A1="Shares Issued","Shares Issued",IF(Month(B1)&Year(B1)=Month(C1)&Year(C1),"MATCH!",""))

Now if the description column says the shares were issued, that's what your formula returns. But if it doesn't say that it checks if the dates match. If the dates match it tells you! If not, then it just returns blank, to make viewing your data much easier.

Hope this helps!