Date formula for Last month, Last Quarter, Last year

SueinSpain

I'm looking to add some formula to my summary sheet and the requirement is for analytics for Last month, last quarter and last year - so excluding current month

Examples if:

current month = May 2020

last month = April 2020

last quarter = February, March & April 2020

last year = May 2019 to April 2020 inclusive

These would all be rolling so change when current month becomes June 2020 then last month = May 2020, Last quarter = March to May 2020 and Last year = June 2019 to May 2020

1. I need to be able to count the number of entries in a column (COUNTIF is great) but where the Date Form Raised date field) is last month. - I thought of using MONTH and TODAY but cannot get to work
2. I then need to do the same thing but for the previous rolling 3 months
3. Finally I need to do this for the previous rolling year

Having got to grips with all this then I can add in networkdays between dates in a row to work out average SLAs...... or just banish user requests.

Hope you super formula gurus can help 😎

Kindest regards

Sue

Paul Newcome

Try these...

Previous month would be along the lines of...

=COUNTIFS([Date Column]:[Date Column], AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)))

Rolling year would be

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

Rolling Quarter:

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

Paul Newcome

Please let me know how they work!

Paul Newcome

Try these...

Previous month would be along the lines of...

=COUNTIFS([Date Column]:[Date Column], AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)))

Rolling year would be

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

Rolling Quarter:

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

SueinSpain

WOW just back from short holiday so will give these a try but a great help to get me started anyway. Thanks so much for the support - really appreciated.

Paul Newcome

Please let me know how they work!

SueinSpain

Just tried the first one ... AND IT WORKED just need to get my head around how and why so I can learn.

Sorry for the delay in response but take a couple of days off and the backlog is a nightmare 🤣

Will let you know how I get on with the others but brilliant so far thanks very much for your help

SueinSpain

Hi Paul,

Just completed testing all 3 formulas and thanks once again for the help & support.

The previous month and year work brilliantly and match the totals I get when I double checked by filtering the records.

However, the previous 3 months / quarter didn't work as gave same result as for the year.

I've included screenshots of the full details in case I have made an error when putting the formula in.

The total for last month = 12 is correct

The total for the last 12 months (Year) = 115 is correct

But the total for last 3 months (Quarter) = 115 is incorrect

Paul Newcome

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)

RoofingRevolution

@Paul Newcome Hey this is great information, Thank you! Is there anyway you can adjust one for a MTD?

Michael Wilkesen

https://community.smartsheet.com/discussion/comment/251118#Comment_251118

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 Newcome

There are two different ways to do this depending on exactly what you are trying to accomplish. My first suggestion would be to add the range/criteria to the COUNTIFS formulas.

=COUNTIFS([Department ID]:[Department ID], 12345, [Date Range]:[Date Range], ............................)

Michael Wilkesen

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

Thank for your help!

CAH

@Paul Newcome I want to adapt a formula so that it refers to the next 'January' from the start date. If 'Analysis start' date is January 23, I want it to refer to January 23 but if start date is February 23, it refers to February 24. And then, if 'Analysis start' is January 24, I want it to refer to January 24 but if start date is February 24, it refers to February 25. This is the formula for the current year but I don't want to have to update it every year ongoing. It breaks down cost into months evenly based on start/finish dates so we can forecast project revenue into months and with a solution to this, I would then apply it to every month such as for November, if start date is November 23, then use November 23 but if start date is January 24, use November 24. I hope that this makes sense and really appreciate any help!

Jan Formula:

=IFERROR(IF(AND([Count Ancestors]@row = 0, [Daily Amount]@row = ""), IF(AND(Discount@row = "", SUM(CHILDREN()) > 0), SUM(CHILDREN()), IF(AND(Discount@row <= 1, SUM(CHILDREN()) > 0), (1 - Discount@row) * SUM(CHILDREN()), IF(AND(Discount@row > 1, SUM(CHILDREN()) > 0), SUM(CHILDREN()) - (Discount@row * (SUM(CHILDREN()) / [Subtotal Price]@row))))), IF(([Daily Amount]@row * ((MAX(NETWORKDAYS([Analysis Start]@row, DATE(2023, 1, 31)), 0) - MAX(NETWORKDAYS([Analysis Start]@row, DATE(2022, 12, 31)), 0)) - (MAX(NETWORKDAYS([Analysis Finish]@row, DATE(2023, 1, 31)), 0) - MAX(NETWORKDAYS([Analysis Finish]@row, DATE(2022, 12, 31)), 0)) + IF(MONTH(DATE(2023, 1, 31)) = MONTH([Analysis Start]@row), 1))) > 0, [Daily Amount]@row * ((MAX(NETWORKDAYS([Analysis Start]@row, DATE(2023, 1, 31)), 0) - MAX(NETWORKDAYS([Analysis Start]@row, DATE(2022, 12, 31)), 0)) - (MAX(NETWORKDAYS([Analysis Finish]@row, DATE(2023, 1, 31)), 0) - MAX(NETWORKDAYS([Analysis Finish]@row, DATE(2022, 12, 31)), 0)) + IF(MONTH(DATE(2023, 1, 31)) = MONTH([Analysis Start]@row), 1)))), "")