December 15, 2016
Hi fellow Excel Addict,
Officially it's not winter in Newfoundland for another week but this morning I think we can safely say "WINTER IS HERE!".
Thanks for Your Support
I want to thank you for your support of my newsletter and website over the past year. Your enthusiasm for wanting to improve the way you use Excel by continually learning new and better ways is what keeps me doing this.
You may have heard me talk before about how frustrating it was for me in my previous career where the majority of my co-workers and bosses showed little interest in using Excel's advanced capabilities to advance the way we were doing things (in a finance department no less).
So it's such a privilege to have thousands of like-minded 'Excel Addicts' who really do appreciate and understand that Excel really does give us unlimited possibilities. We just have to keep imagining, keep learning and keep Excelling.
Merry Christmas & Happy New Year
If you are celebrating Christmas, I want to wish you, your family and friends a joyful and safe Christmas and New Year. I will be starting my newsletters back up again on January 17th.
My Last Tip for 2016
In today's 'Excel in Minutes' tip I'm going to show you how to 'Add A Dynamic Date Range Title To Your Report'.
If you missed my 'Excel in Seconds' newsletter on Tuesday, I showed you that 'You Can Use This Help With Worksheet Functions'. You can read that tip here.
I hope you have a great week and keep on Excelling,
Keep on Excelling,
Francis Hayes (The Excel Addict)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
Add A Dynamic Date Range Title To Your Report
We often include in the titles of our reports the time period covered by the report.
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.
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.
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.
If column B doesn't contain any other data besides the dates and maybe a column heading, you could simplify your formula with...
2) To return the latest date from the dates column, you can use the MAX function like this...
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.
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
Now, whenever this report is updated with new data and dates, the report title will automatically update the date range.
|"Spreadsheets Tips From An
Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630