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

Hobson_K

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.

Find more posts tagged with

Accepted answers

Kelly Moore

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

All comments

Kelly Moore

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

Will this work for you?

Kelly

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

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?

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.