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

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 Newcome
    Paul Newcome ✭✭✭✭✭✭
    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.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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.

  • Diane Moore
    Diane 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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!