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

Options

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.


Best Answer

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

    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

Answers

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

    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

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    Options

    @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


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

    Facilityy Professional Services Inc.

    http://www.facilityy.com

  • Hobson_K
    Options

    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?

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!