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