How to subtract a Month and Year from a Joined column?

Hobson_K
Hobson_K ✭✭
in Formulas and Functions

Hello,

What I need to do is subtract 1 month and for the corresponding year to adjust.

In 1 column called App Window I have the formula to join two other columns.

=JOIN([App Month]@row + ", " + [App Year]@row)

In the next column, all I would need to do is go back 1 month.

For example: January, 2023 -> December, 2022 or February, 2022 -> January, 2022

Is it possible to avoid using days? subtracting 30 or 28 days still sends me back 2 months depending on the date.

image.png


· Share on FacebookShare on Twitter

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Hobson_K

    If you don't have this already, I would add a helper column [Last MonthNum]

    [Last MonthNum]

    =IF(MONTH([Date of App Window]@row) = 1, 12, MONTH([Date of App Window]@row) - 1)

    For your Last Month/Year string

    *I assumed you used a nested IF to get your original Month names. If instead you have a lookup table we can use that again. Rather than do the Month-Name and Year in two columns, I did it one

    =IF([Last MonthNum]@row = 1, "January " + VALUE(YEAR([Date of App Window]@row)), IF([Last MonthNum]@row = 2, "February " + VALUE(YEAR([Date of App Window]@row)), IF([Last MonthNum]@row = 3, "March " + VALUE(YEAR([Date of App Window]@row)), IF([Last MonthNum]@row = 4, "April " + VALUE(YEAR([Date of App Window]@row)), IF([Last MonthNum]@row = 5, "May " + VALUE(YEAR([Date of App Window]@row)), IF([Last MonthNum]@row = 6, "June " + VALUE(YEAR([Date of App Window]@row)), IF([Last MonthNum]@row = 7, "July " + VALUE(YEAR([Date of App Window]@row)), IF([Last MonthNum]@row = 8, "August " + VALUE(YEAR([Date of App Window]@row)), IF([Last MonthNum]@row = 9, "September " + VALUE(YEAR([Date of App Window]@row)), IF([Last MonthNum]@row = 10, "October " + VALUE(YEAR([Date of App Window]@row)), IF([Last MonthNum]@row = 11, "November " + VALUE(YEAR([Date of App Window]@row)), IF([Last MonthNum]@row = 12, "December " + VALUE(YEAR([Date of App Window]@row) - 1)))))))))))))

    Will this work for you?

    Kelly

    · Share on FacebookShare on Twitter

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Hobson_K

    If you don't have this already, I would add a helper column [Last MonthNum]

    [Last MonthNum]

    =IF(MONTH([Date of App Window]@row) = 1, 12, MONTH([Date of App Window]@row) - 1)

    For your Last Month/Year string

    *I assumed you used a nested IF to get your original Month names. If instead you have a lookup table we can use that again. Rather than do the Month-Name and Year in two columns, I did it one

    =IF([Last MonthNum]@row = 1, "January " + VALUE(YEAR([Date of App Window]@row)), IF([Last MonthNum]@row = 2, "February " + VALUE(YEAR([Date of App Window]@row)), IF([Last MonthNum]@row = 3, "March " + VALUE(YEAR([Date of App Window]@row)), IF([Last MonthNum]@row = 4, "April " + VALUE(YEAR([Date of App Window]@row)), IF([Last MonthNum]@row = 5, "May " + VALUE(YEAR([Date of App Window]@row)), IF([Last MonthNum]@row = 6, "June " + VALUE(YEAR([Date of App Window]@row)), IF([Last MonthNum]@row = 7, "July " + VALUE(YEAR([Date of App Window]@row)), IF([Last MonthNum]@row = 8, "August " + VALUE(YEAR([Date of App Window]@row)), IF([Last MonthNum]@row = 9, "September " + VALUE(YEAR([Date of App Window]@row)), IF([Last MonthNum]@row = 10, "October " + VALUE(YEAR([Date of App Window]@row)), IF([Last MonthNum]@row = 11, "November " + VALUE(YEAR([Date of App Window]@row)), IF([Last MonthNum]@row = 12, "December " + VALUE(YEAR([Date of App Window]@row) - 1)))))))))))))

    Will this work for you?

    Kelly

    · Share on FacebookShare on Twitter
  • Brent Wilson
    Brent Wilson ✭✭✭✭✭

    @Hobson_K

    First, do it off the app Window date .. it is too hard to go from Date to Text and back to Date and then Back to Text

    =IF(MONTH([Date of App Window]@row) = 1, DATE(YEAR([Date of App Window]@row) - 1, MONTH([Date of App Window]@row) + 11), DATE(YEAR([Date of App Window]@row), MONTH([Date of App Window]@row) - 1))

    This checks to see if the month is January and adjusts the math accordingly. As you seem to be going for Months and Years I left the Date out. The formula above will set the new date to be the first of the month

    Let me know I have a little vlookup trick to get the 12th month to return December via a formula. I can walk you through that

    image.png


    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

    · Share on FacebookShare on Twitter
  • Hobson_K
    Hobson_K ✭✭
    https://community.smartsheet.com/discussion/comment/340505#Comment_340505

    Thank you, Kelly!!! This works PERFECTLY.

    Can I ask about this formula?  1, 12, MONTH([Date of App Window]@row) - 1) Is this formula stating if the Month is January or December just minus 1?

    · Share on FacebookShare on Twitter
  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey

    The formula says If the Month= 1 then return 12, else use the equation Month -1. This easily takes into account the one month where we need to force the response. You'll see the same thing happen in my Join statement. We know that if Month-1 has equaled December then the Year is going to be Year - 1. So I make that correction as the Join is happening.

    If I didn't explain that well, ask me again and I'll try a different way. Questions are always good in the community.

    Kelly

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!