Formula for "Year + Month" date calculation

Filippo

We currently have a sheet with an automation to update a cell with TODAY's date.

Then there are columns with formulas (see below) to calculate 1 MONTH, all the way back to 12 MONTHS prior to the current date.

Formula to calculate 1 MONTH prior. Please note that we calculate the beginning of the new month not on the 1'st, but the 16'th of each month. That is why you see <16 below.

=MONTH(Today@row) - COUNTIF(DAY(Today@row), <16) + (12 * MAX(COUNTIF(MONTH(Today@row), 1), COUNTIF(Today@row, AND(DAY(@cell) < 16, MONTH(@cell) < 3)))) - 1

For Months 2 to 12, we use the following formula:

(We basically look at the prior month, and remove 1 from it)

=IF([1 Month Back - Month]@row = 1, 12, [1 Month Back - Month]@row - 1)

I would like to auto-calculate the YEAR, but I am not sure how to do that....

• If you look at the screenshot below, for "1 Month Back - Month" we have 2, meaning February, so I would expect the column named "1 Month Back - Year" to read 2023
• Similar expectations for column "2 Months Back - Month" with 1 for January, which should also read 2023.
• However, for column "3 Months Back - Month", since we have 12, the year should be 2022.

Peggy Parchert

@Filippo

Will this work?

Peggy

Peggy Parchert

Hello @Filippo -

I have a separate metric sheet I that I use for dates - converting to text and extracting Month / Year

I use this is several of my sheets/projects so that as the date changes (TODAY Helper column) my sheets/project update (roll). Saves me time from having to manually update things when the month rolls.

COLUMNS:

TODAY Helper: this is a date type column and is auto-populated by a workflow to record a date.

Count: This is a text type column and the numbers do not change

Month: This is a text type column

• formula: =IF((MONTH([TODAY Helper]@row) + Count@row) < 13, MONTH([TODAY Helper]@row) + Count@row, MONTH([TODAY Helper]@row) + Count@row - 12)

Month Convert: This is a text type column

• formula: =IF(Month@row = "12", "DEC", IF(Month@row = "11", "NOV", IF(Month@row = "10", "OCT", IF(OR(Month@row = "9", Month@row = "09"), "SEP", IF(OR(Month@row = "8", Month@row = "08"), "AUG", IF(OR(Month@row = "7", Month@row = "07"), "JUL", IF(OR(Month@row = "6", Month@row = "06"), "JUN", IF(OR(Month@row = "5", Month@row = "05"), "MAY", IF(OR(Month@row = "4", Month@row = "04"), "APR", IF(OR(Month@row = "3", Month@row = "03"), "MAR", IF(OR(Month@row = "2", Month@row = "02"), "FEB", IF(OR(Month@row = "1", Month@row = "01"), "JAN"))))))))))))

Month Convert2: This is a text type column

• formula: =IF([Month Convert]@row = "DEC", "12", IF([Month Convert]@row = "NOV", "11", IF([Month Convert]@row = "OCT", "10", IF([Month Convert]@row = "SEP", "09", IF([Month Convert]@row = "AUG", "08", IF([Month Convert]@row = "JUL", "07", IF([Month Convert]@row = "JUN", "06", IF([Month Convert]@row = "MAY", "05", IF([Month Convert]@row = "APR", "04", IF([Month Convert]@row = "MAR", "03", IF([Month Convert]@row = "FEB", "02", IF([Month Convert]@row = "JAN", "01"))))))))))))

Year: This is a text type column

• formula: =IF((OR((MONTH([TODAY Helper]@row) + Count@row) > 12, Count@row = 0)), YEAR([TODAY Helper]@row), YEAR([TODAY Helper]@row) - 1)

Converted Date: This is a text type column

• formula: =JOIN([Month Convert2]@row:Year@row, "/")

If you wanted Mar 2023 instead of 03/2023, you would use the Month Convert column instead of Month Convert2 and move it next to Year column so the JOIN formula would work.

Note: the Converted Date is not a Date but text.

Convert Date (date): This is a date type column

• formula: =DATE(VALUE("20" + RIGHT([Date Convert]@row, 2)), VALUE(LEFT([Date Convert]@row, FIND("/", [Date Convert]@row) - 1)), VALUE(MID([Date Convert]@row, FIND("/", [Date Convert]@row) + 1, FIND("/", [Date Convert]@row, FIND("/", [Date Convert]@row) + 1) - (FIND("/", [Date Convert]@row) + 1))))

Hope this helps.

Peggy

Filippo

Thank you for those formulas. I am sure they can be very helpful, but they don't solve my current issue.

If you look at the column formulas under:

1 Month Back - Month

2 Months Back - Month

3 Months Back - Month

We are auto-calculating the three month columns, using the current date cell. If you look at the screenshot above, the date was 03/24/23. I am not trying to get the month and year from 03/24/23, but I am trying to get the year from the month formula column. For example, the column "1 Month Back - Month" has "2" for the month. I need the Year column to auto-calculate the year for "2" which should be 2023.

Peggy Parchert

@Filippo

Will this work?

Peggy

Filippo

Thank you Peggy, those formulas are exactly what I needed!

Problem solved.

Peggy Parchert

Glad those worked! Have a great day!

Peggy

Filippo

Hey Peggy,

I found a problem with the above formulas, for the month of "December". As you can see from the attached screenshot, it is calculating the YEAR as 2023 instead of 2022.

I have to admit that i don't understand the logic in the formulas, but i have tried to change the "Current Month" from +11 to +12, but that won't work, because "December" will be +10 instead of +9, which is still calculated as 2023 as the YEAR.

Do you think I should start "Current Month" at +10, so that "December" is +8, hence showing 2022 as they year?

I know that works for now, with a current date of 4/4/2023. But would something brake down the road as the current date changes?

Peggy Parchert

@Filippo

I can't read your image - it is too small but I went back over everything.

I've attached an excel spreadsheet with the formulas. I realize that your setup is horizontal across a sheet but for ease in viewing, I did this vertically. I did test these formulas out with a setup just like your sheet (horizontal) and they all work.

Note: I created Sheet Summary fields that are used in the formulas - these show on a separate tab in the excel sheet.

Formulas for year plus month.xlsx

Let me know if you have questions/issues.

Thanks

Peggy

Filippo

I have created an excel version of the sheet and attached the same here.

Perhaps I should have displayed the data vertically, as you did, but I wanted to give you the real experience of what I am looking at.

As a reminder, although we are in "April" we are reconciling last month's financial data, so the "Current Month" is really "March" for us. Furthermore, to make this more fun, the month for our calculations starts on the 16'th day, so on April 16 the "Current Month" is MARCH, while on April 15, the current month is FEBRUARY.

The MONTH calculation work correctly on your previous formulas, so i don't think we need to change those. However, something is amiss with the YEAR formula, because for December it shows a year of 2023. See column "V" on the attached spreadsheet, named "2 Months Back - Year".

Smartsheet Month-Year Counts.xlsx

Filippo

@Peggy I noticed that you haven't responded to my message from 4/5, perhaps because i forgot to include the "@" symbol next to your name. Can you please look at the document i have added on 4/5?

Filippo

@Peggy Parchert sorry perhaps i had included the wrong Peggy in this post. See my latest note above from 4/5, i hope you can help!

Peggy Parchert

@Filippo - my apologies. I've had a family emergency come up and haven't been able to get back to this.

@Andrée Starå - would you have time to assist with this? I can't seem to figure out why the year formula isn't working. I used the below formula with no issues (from my original post above)

Year: This is a text type column

• formula: =IF((OR((MONTH([TODAY Helper]@row) + Count@row) > 12, Count@row = 0)), YEAR([TODAY Helper]@row), YEAR([TODAY Helper]@row) - 1)

Thank you!

Peggy