Formula for "Year + Month" date calculation
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 autocalculate 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.
Best Answer
Answers

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 autopopulated 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

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 autocalculating 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 autocalculate the year for "2" which should be 2023.


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

Glad those worked! Have a great day!
Peggy

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?

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.
Let me know if you have questions/issues.
Thanks
Peggy

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".

@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?

@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!

@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
Help Article Resources
Categories
Check out the Formula Handbook template!