The Excel Addict - Add A Dynamic Date Range Title To Your Report

Home Recent Posts Newsletter Training Add-Ins Testimonials About

Add A Dynamic Date Range Title To Your Report

by Francis Hayes (The Excel Addict)

We often include in the titles of our reports the time period covered by the report.

Report Title With Date Range in Microsoft Excel 2007 2010 2013 2016 365

It's so easy to simply type the dates into the report's title since you only need to do it once each period. That's not such a big deal, right? But why do that if there is a way to have your report title automatically updated based on the range of dates in your report?

Thankfully, this can be done pretty easily with the help of a few Excel functions.

For example, if column B in your report has a range of dates, you can add a title to your report that shows the date range from the earliest to the latest date in that column.

Here's how...

1) Assuming the dates in column B start on row 5 and end on row 15, you can use the MIN function to return the earliest date in that range.

=MIN(B5:B15)

If the number of rows varies each month, you can give yourself some extra room by using a cell reference beyond the range that your report would possibly use, assuming there is no other data below in that column.

=MIN(B5:B10000)

If column B doesn't contain any other data besides the dates and maybe a column heading, you could simplify your formula with...

=MIN(B:B)

2) To return the latest date from the dates column, you can use the MAX function like this...

=MAX(B5:B15)
or =MAX(B5:B10000)
or =MAX(B:B)

3) These two functions could be placed in separate cells for your report title to give you 'from' and 'to' dates but, to make it look more professional, it's best combine them in one cell. Combining multiple numeric values in a single text string can be accomplished using the TEXT function and the ampersand (&) to join the two text strings.

=TEXT(value, display_format)

The TEXT function lets you converts numeric values to text by specifying special formatting codes. This is great for situations where you want to combine text and numbers referenced from your worksheet and place them in a single cell.

It this example, we can create a text string of the earliest date using this formula...

=TEXT(MIN(B5:B15),"mmm d, yyyy")

...and for the latest date, the formula would be...

=TEXT(MAX(B5:B15),"mmm d, yyyy")

To combine these two functions into one cell we can use...

="Period : " & TEXT(MIN(B5:B15),"mmm d, yyyy")&" to "
&TEXT(MAX(B5:B15),"mmm d, yyyy")

... to get this result...

Period : Oct 2, 2016 to Dec 11, 2016

Report Title With Automatically Updating Date Range in Microsoft Excel 2007 2010 2013 2016 365

Now, whenever this report is updated with new data and dates, the report title will automatically update the date range.

If you found this tip helpful, please share it with your friends and colleagues.

To get more tips every week like this one...

Sign up for my FREE twice-weekly Newsletter
'Spreadsheet Tips From An Excel Addict'
'Excel in Seconds' & 'Excel in Minutes'

Plus you also get my 'Excel in Seconds' E-book as a BONUS!

(Download it immediately after you sign up)

Home Recent Posts Newsletter Training Add-Ins Testimonials About