Month Today formula for previous year's month?

I'm curious to see if there is a formula out there that I am missing. Currently this formula takes this current year and subtract one from it and also references a month based on a monthly column. So instead of having to reference 2024 in the formula I can say year today minus one to reference that year. I'm curious is there a way to do that for the months of the previous year so I wouldn't have to have the "01"in this formula?
=COUNTIFS({Community Requests Range 4}, "01", {Community Requests Range 3}, YEAR(TODAY()) - 1)
Currently, I have to create columns for all of my previous months of the year. In January I would have a formula that has the "01" to ref jan, "02" to ref Feb, etc. So at the end of one year, I need to paste the January formula into a new column to reference the next year is there a way to have the January cell automatically know it is January (ex. Month(Today()-1) for that previous year based on the current month I'm in? I'm not sure if this is even possible since the current month always changes? Thank you for any information.
Answers
-
Completely get what you mean and I had awesome help from this group to define formulas for previous months catering for the year change so see if these help.
I also use helper fields to this month, last month etc. with true or False to simplify formula and easier to cross checl or refer to in filters as well. so rather than having to keep changing the filter on a report for last month's dates I can just check for records where the field [Last Month] = "True"
Good luck and come back to me if you need more help as these are just the compliations of some great advice & support from the community
Column formula for identifying this month & last month (year swap still outstanding)
This Month
=IF(DATE(YEAR([Requested On]@row),MONTH([Requested On]@row),1) = DATE(YEAR(TODAY()),MONTH(TODAY()),1)), "True", False")
Last Month
=IF(DATE(YEAR([Requested On]@row), MONTH([Requested On]@row), 1) = DATE(YEAR(TODAY()), (MONTH(TODAY()) - 1), 1), "True", "False")
Summary Fields formula
NEW Vendor
=COUNTIF([Record Type]:[Record Type], ="NEW Vendor")
This Month
=COUNTIFS([Record Type]:[Record Type], ="NEW Vendor", [Date Form Raised]:[Date Form Raised], AND(IFERROR(MONTH(@cell ), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)), IFERROR(YEAR(@cell ), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)))@cell
Last Month
=COUNTIFS([Record Type]:[Record Type], ="NEW Vendor", [Date Form Raised]:[Date Form Raised], 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)))@cell
Last 3 months
=COUNTIFS([Record Type]:[Record Type], ="NEW Vendor", [Date Form Raised]:[Date Form Raised], 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)))
Sue Rogers
AmerisourceBergen - MWI Animal Health
Business Analyst
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.4K Get Help
- 430 Global Discussions
- 152 Industry Talk
- 492 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 76 Community Job Board
- 504 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!