Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Month Today formula for previous year's month?

✭✭✭✭
edited 02/13/25 in Formulas and Functions

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions