When we last left our heroes in Excel Video 166, we had a clever formula that automatically updated after entering data in one cell. In Excel Video 167, we’ll go one step further and automatically calculate that cell as well. When you open the spreadsheet now, all of the cells will have the months you need. To get the first cell to automatically update, we’ll combine some functions we just learned, TODAY, DATE, YEAR, and MONTH.
Here’s the formula for the first cell (that references TODAY):
=DATE(YEAR(TODAY()),MONTH(TODAY()),1)
Remember that you can always subtract 1 from the MONTH part of the formula to get last month or subtract 1 from the YEAR to get last year.
Here’s the formula for each of the other months in the report.
=DATE(YEAR(M4),MONTH(M4)-1,1)
Remember that this formula subtracts one from the month above it, so the most recent month is on top. You could just as easily add one to the month so that the most recent month is at the bottom of your report.
Finally, I’ll show you another custom date format that makes the dates look better. I hope you found the formulas helpful.
