True value if during the last 12 months

kioshi43

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

@kioshi43

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.

kioshi43

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

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.