How to automate dates in an Excel title from your dataset or chart table

Are you tired of constantly changing dates in your Excel report titles?  Perhaps you have been embarrassed by forgetting to update the title before emailing your Excel report out?  Well no more, automate the title so you can forget about it forever…

If you create a report in Excel for other users then it is good practice to have a title on the worksheet so people can quickly understand what they are looking at.  Another excellent practice, especially with regular reports, is to include the date range in question in the title; again this helps the user quickly see if they are looking at a recent report.

A basic report might look something like:

Excel How To Automate Dates in Titles 1

One of the main flaws with reports like this is that Excel users can often forget to change the date range in the title.  We’ve all done it, rushing to get a report to a manager we update the Excel file and before we realise it the email is sent and the first we hear of our mistake is when the manager comes back to point it out…that won’t do our reputation any good now will it!

The easiest way to stop this ever happening is to automate the dates in the title from the data, in this case our data table which is feeding the chart, but equally it could be your data on another worksheet.

For this example our date range title is in cell A2 so we need to change this cell to a formula as follows:

Excel How To Automate Dates in Titles 2

The changes you would need to make are the location of your date range.  In my example the dates are contained within the chart table in column I.

We use the MIN and MAX function as this will ensure next month when we have Sep-11 in cell I15 it will pick up Sep-11 as the end date, and so on each month we produce the report.

We also use the TEXT function because otherwise Excel will return the dates in Excel date number format, for example if you don’t convert to text it will show:

Excel How To Automate Dates in Titles 3

And with regards to the formatting of the text in this case I have chosen to display the date in MMM-YY format, but you can change this to your preferred format.

For example if you would rather the full month name and full year is shown, January 2011 rather than Jan-11, then just change your formula to look like:

=”Data from “&TEXT(MIN(I1:I1000),”MMMM YYYY”) & ” to ” & TEXT(MAX(I1:I1000),”MMMM YYYY”)

And it’s as simple as that, never again will you send out a report with mistakes in the title!

Keep Excelling,

1 Comment

  1. Pingback: How to link to cells on different worksheets in Excel | dedicatedexcel.com

Leave a Comment