Excel Video 166 reviews four very basic Excel date functions, but we can combine the functions to make rolling reports update automatically. DATE is a function that lets you enter the year, month, and day separated by commas. Excel turns that information into a date. YEAR, MONTH and DAY simply look at a date in Excel and return the respective year, month, or day portion of the date. So far, everything is not only easy but you’re probably wondering why Excel even has such simple functions.
I’ll show you a trick I’ve used on rolling reports that requires these functions. By rolling report, I mean a report that shows the last x months, like the last 12 months of collections. At the end of each month, a new month of data is added to the report and the oldest month of data drops off. The report rolls forward to capture the new month’s data but still only shows 12 months of data on the report.
I’ll demonstrate three different ways to get Excel to quickly display 12 months. None of the methods are very complicated. I’ll usually enter a few cells of data to establish a pattern for Excel to follow and then drag down to fill the cells I need. Whether you drag to fill down, use the fill series option or use the formula I’ll demonstrate, you get the same results. The trick is that when it comes time to update the data for a rolling report, if you use the formula I demonstrate, you change one cell and your data will update automatically.
That’s not quite good enough. Stay tuned next time and we’ll create a formula so you don’t even have to change one cell for the rolling report to automatically update.
