Date formula for Last month, Last Quarter, Last year
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
如果例子:
current month = May 2020
上个月= 2020年4月
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
- 我需要我计算条目的数量n 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
- I then need to do the same thing but for the previous rolling 3 months
- 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
Sue Rogers
AmerisourceBergen - MWI Animal Health
Business Analyst
Best Answers
-
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 ✭✭✭✭✭✭
Answers
-
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.
Sue Rogers
AmerisourceBergen - MWI Animal Health
Business Analyst
-
Paul Newcome ✭✭✭✭✭✭
-
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
Sue Rogers
AmerisourceBergen - MWI Animal Health
Business Analyst
-
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
Sue Rogers
AmerisourceBergen - MWI Animal Health
Business Analyst
-
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)
-
@Paul NewcomeHey this is great information, Thank you! Is there anyway you can adjust one for a MTD?
-
Michael Wilkesen ✭✭✭✭
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,(日期响了e]:[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 NewcomeI 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)))), "")
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-26T01:04:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":22,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-26T01:04:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"