 |
|
| TheExcelAddict.com | |
March 12, 2020 |
|
| Hi fellow Excel Addict, | |
 In today's 'Excel in Minutes' tip I will show you how to create 'Report Heading Dates That Update Dynamically'. If you like it, please share it on social media and with your colleagues or any other Excel users you know. And while you're at it, please recommend that they sign up for my newsletter. Thanks in advance and STAY SAFE!. Francis Hayes (The Excel Addict) Email: fhayes[AT]TheExcelAddict.com |
|
|
|
|
|
|
|
| Missed my last newsletter? | |
Click Here to View it Online |
|
| |
|
Having a positive attitude can help us in difficult times. I hope today's quote will help you foster a positive attitude today. Quote of the Day "Challenges are what make life interesting; overcoming them is what makes life meaningful." -- Joshua J. Marine -- |
|
|
If you have a favourite quote, send it to me
and I may post it in my newsletter.
|
|
THIS WEEK'S 'EXCEL IN MINUTES' TIP |
|
Report Heading Dates That Update Dynamically |
|
 It's 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 wouldn't you like to have your report titles updated automatically based on the 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 contains a range of dates covered by the report, 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 14, 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 (aslo see With Excel Tables note below), assuming there is no other numeric data below in that column. =MIN(B5:B10000)
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) 3) These two functions could be placed in separate cells for your report title to give you 'from' and 'to' dates but instead you may want to combine them in a single cell. In order to format numbers in a text string you'll need to use the TEXT function. =TEXT(value,
display_format)
The TEXT function lets you converts numeric values to text by applying special formatting codes. For this example, we can convert the earliest date in the range B5:B15 to text 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")
For situations where you want to include both descriptive text and values referenced from your worksheet and place them into a single cell, we need to use the ampersand (&) to combine the parts of the text string. To combine these two functions into one cell we can use... ="Reporting
Period : " & TEXT(MIN(B5:B15),"mmm d, yyyy")&"
to "
&TEXT(MAX(B5:B15),"mmm d, yyyy") ... to get this result... Reporting
Period : Jan 4, 2020 to Mar 7, 2020
 Now, whenever your report is updated with new dates and data, the report title will automatically update for the new date range. The above is just an one illustration of how to include dates in your report headings that will update as you update your worksheet. I hope you're starting to see that this technique can be used in almost any situation where you want to combine some fixed text with one or more values from your worksheet for which you want to control the formatting and put it into a single cell. With Excel Tables This technique becomes even easier when you use it on an Excel Table, since your formula will adopt the structured references from the Table and will therefore automatically adjust as rows are added or deleted from the Table. |
|
|
|
|
|
|
|
 |
|
 |
|
| 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 16 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. | |