| A
FREE Excel
Tips
Newsletter
from TheExcelAddict.com Click here to get more tips like this every week |
December 15, 2016
Officially it's not winter in Newfoundland for another week but this morning I think we can safely say "WINTER IS HERE!".
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)
Email: fhayes[AT]TheExcelAddict.com
If you missed my last newsletter, you can click here to view it online.
| TheExcelAddict.com
Quote of the Day "Start by doing what’s necessary; then do what’s possible; and suddenly you are doing the impossible." -- St. Francis of Assisi -- |
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.
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)
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")
&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.
| A
FREE Excel
Tips
Newsletter
from TheExcelAddict.com Click here to get more tips like this every week |
| Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose. |
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630