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

08/25/21
Accepted

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.

Tags:

Best Answer

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    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.

    thinkspi.com

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    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.

    thinkspi.com

  • Diane MooreDiane Moore ✭✭✭✭

    @Paul Newcome - Thank you so much. I have implemented your suggested changes and everything is working as expected. I really appreciate your prompt assistance!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com

Sign In or Register to comment.