Date formula for Last month, Last Quarter, Last year

My apologies. The rolling quarter should actually be 3 and 9.

=COUNTIFS([Date Column]:[Date Column], AND(@cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 9, 1)), @cell < DATE(YEAR(TODAY()), MONTH(TODAY()), 1)))

Here's a quick breakdown of this particular one...

We actually have 3 different dates being calculated in this formula, but only two will be used at a time.

The first date is going to be the beginning of your date range. The beginning of the range in this case is the 1st of 3 months ago. So if we are currently in May, we want to go back to the 1st of February in the current year.

DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1)

The problem we run into with that is if the current month is February, simply subtracting 3 from the current month number gives us -1. There is no month number of -1, so we need to go back to next year instead. Since there are 12 months and we are looking back 3...

12 - 3 = 9

So taking off 12 months (by subtracting a year) means we need to add 9 months to get back to that -3 (hopefully that makes sense).

DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 9, 1)

Since the first date calculation has the potential to throw an error, we can use an IFERROR statement to say that we want to run the first calculation first, but if it throws an error then run the second.

IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 9, 1))

@cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 9, 1))

Finally the 3rd date calculation is establishing the end of our date range. Instead of creating a convoluted mess to establish the last day of the previous month accounting for 28, 29, 30, or 31 days in the various months, we can just generate the 1st of the current month and say anything that is less than (but not equal to) it.

DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

@cell < DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

Now that we have the beginning and end dates for our ranges, we drop them into an AND statement

AND(@cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 9, 1)), @cell < DATE(YEAR(TODAY()), MONTH(TODAY()), 1))

and use that as the criteria for counting the dates in our date column

=COUNTIFS([Date Column]:[Date Column], criteria)

=COUNTIFS([Date Column]:[Date Column], AND(@cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 9, 1)), @cell < DATE(YEAR(TODAY()), MONTH(TODAY()), 1)))

Then we use the same concept of establishing the range of dates we want to count for the previous year and previous month. The end date is the same. Less than the 1st of the current month.

The start date for the rolling year is pretty straight forward.

We go back 1 year and use the 1st of the current month.

DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1)

For the previous month, we go tho the first of the current month and subtract 1 day (last day of the previous month).

DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1

Then we pull the MONTH and YEAR for that date.

MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)

YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)

Paul, as always, this is great. I have implemented this myself and all works perfectly for me; however I would like to add 1 additional condition to the equation that I cannot seem to work out. I would like to add the condition to return the value as assigned to a "Department ID" number (or team member). Basically "if {Department ID}="12345," then perform the rolling quarter calculation, or perform the rolling quarter calculation for {Department ID} "12345" and advice would be welcomed!

Paul, I don't know what I was doing wrong, because I "THOUGHT" I had done exactly as you suggested!

Thank for your help!