True value if during the last 12 months

Hi all,

I keep getting turned around in trying to build a formula to return a true value if the year/month is within the last 12 months.

What I would like to do is check off which rows should be included in the rolling report so that as the year goes on, the last 12 months are always checked. Anyone have any thoughts on it?



  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭


    Since you're not working with actual date values, we have to get creative!

    I'm thinking that we construct a real date value out of the YYYY.DD values and compare that to TODAY(-365).

    =IF(AND(DATE(VALUE(LEFT(Date@row, 4)), VALUE(MID(Date@row, 5, 2)), DAY(TODAY())) >= TODAY(-365), DATE(VALUE(LEFT(Date@row, 4)), VALUE(MID(Date@row, 5, 2)), DAY(TODAY())) <= TODAY()), 1, 0)

    This uses the DATE function (syntax =DATE(yyyy, mm, dd)) to construct a date from the value of the first 4 characters in the Date column (year,) the value of the 5th and 6th characters in the Date column (month,) and the current day of the month (day.) Then it checks if that date value is greater than or equal to the date 365 days ago, and less than or equal to today, and if it is, check the box.

    There are plenty of other ways to do this, so if this doesn't do what you need, we can try something else.


    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Looks like I am getting an invalid data error when I threw that in. If it helps, the date column is actually a formula of the month and year, just brought together so that it would label the charts on the dashboard accordingly haha

    The primary column has this formula: =IF(Ancestors@row = 0, Year@row, PARENT(Year@row) + "." + Month@row)

  • Walter Mootz
    Walter Mootz ✭✭✭✭
    edited 08/15/23

    I'm getting the invalid error as well with just normal rows, not parent relationships.

    *realized that the syntax was wrong as i have mine in US MM/DD/YY format. Will reformat and inform of result.

    **Reformatted and still had the error.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!