Determine if a Date is equal to or greater than 3 months ago

Diane Moore · 08/25/21

Looking for syntax help. I have columns (This Month, Last Month and Previous Month) to support some dashboard reporting IF statements. I am now looking to add a column called ">=3months" and I am unsure of the syntax.

The syntax below is what I am using to determine Last Month:

=IF(AND(MONTH([Record Date]@row) = MONTH(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, 12, 1))), YEAR([Record Date]@row) = YEAR(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, 12, 1)))), 1)

Would greatly appreciate help to modify this to determine if the Record Date is at least 3 months ago or more.

Paul Newcome · 08/25/21

My suggestion would be a different approach. If you insert a text/number column with this column formula...

=VALUE(YEAR([Record Date]@row) + "" + IF(MONTH([Record Date]@row) < 10, "0") + MONTH([Record Date]@row))

Now you have a number that reflects "yyyymm". If you duplicate this for TODAY()

=VALUE(YEAR(TODAY()) + "" + IF(MONTH(TODAY()) < 10, "0") + MONTH(TODAY()))

You can very easily compare these two numbers. If the date stamp is equal to the TODAY stamp + 1, then it is next month. If they are equal then it is current month. If it is equal to the TODAY stamp - 1 then it is last month.

If the TODAY Stamp minus the Date Stamp is greater than or equal to 3 then it is 3+ months in the past.

So basically...

Check the box for next month:

=IF(VALUE(YEAR(TODAY()) + "" + IF(MONTH(TODAY()) < 10, "0") + MONTH(TODAY())) - VALUE(YEAR([Record Date]@row) + "" + IF(MONTH([Record Date]@row) < 10, "0") + MONTH([Record Date]@row)) = -1, 1)

Current Month:

=IF(VALUE(YEAR(TODAY()) + "" + IF(MONTH(TODAY()) < 10, "0") + MONTH(TODAY())) - VALUE(YEAR([Record Date]@row) + "" + IF(MONTH([Record Date]@row) < 10, "0") + MONTH([Record Date]@row)) = 0, 1)

Previous Month:

=IF(VALUE(YEAR(TODAY()) + "" + IF(MONTH(TODAY()) < 10, "0") + MONTH(TODAY())) - VALUE(YEAR([Record Date]@row) + "" + IF(MONTH([Record Date]@row) < 10, "0") + MONTH([Record Date]@row)) = 1, 1)

3+ Months in the past:

=IF(VALUE(YEAR(TODAY()) + "" + IF(MONTH(TODAY()) < 10, "0") + MONTH(TODAY())) - VALUE(YEAR([Record Date]@row) + "" + IF(MONTH([Record Date]@row) < 10, "0") + MONTH([Record Date]@row)) >= 3, 1)

.

I find it is much easier to change that one little bit across a consistent formula as opposed to having to generate multiple different formulas.

Paul Newcome · 08/25/21