Add A Progressive/Dynamic Date Range Title To Your Report in Microsoft Excel Spreadsheet with Example.
Many times when you make reports in Microsoft Excel, we required a dynamic range of dates because dates in your report varies. However reports are based on information across a range of dates oftenly. In that case, you may want to include that date range in your report's title. You could just write it in every (each) time the dates change...does it take only a few minutes?
Many times when you make reports in Microsoft Excel, we required a dynamic range of dates because dates in your report varies. However reports are based on information across a range of dates oftenly. In that case, you may want to include that date range in your report's title. You could just write it in every (each) time the dates change...does it take only a few minutes?
 |
| Dynamic Date Range |
So it would be better to have a report title that changing updates based on the range of dates in your report? This can be done pretty handly(easily) with the help of a few MS Excel functions or Formulas.
Here we take an example, if column B in your Excel spreadsheet report has a range of dates, here is how to add a title that shows the date range from the earliest to the latest date in that column of your report.
1) Assuming the dates in column B start on row 5 and end on row 150, you can use the MIN function / formula to return the earliest date in the column.
Here we take an example, if column B in your Excel spreadsheet report has a range of dates, here is how to add a title that shows the date range from the earliest to the latest date in that column of your report.
1) Assuming the dates in column B start on row 5 and end on row 150, you can use the MIN function / formula to return the earliest date in the column.
=MIN(B5:B150)
If the number of rows changes/varies each month, you can give yourself some extra time by using a reference beyond the range that your report would possibly use.
=MIN(B5:B10000)
If column B does not have (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 newest(latest)x date from the dates column, you can use the MAX function as given below......
=MAX(B5:B150) or
=MAX(B5:B10000) or
=MAX(B:B)
3) These two functions could be placed in separate cells in your report title to give you 'from' and 'to' dates, but to make it look more professional, it is best combine them in one cellin your report. Combining multiple numeric values in a single text string can be proficient using the TEXT function and the & (ampersand) to concatenate the two text strings.
=TEXT(value,display_format)
The TEXT formula converts numeric values to text and indicates its display formatting. This is great for situations where you want to combine numbers referenced from your worksheet with text and place them in a single cell.
It this example, we can show the earliest date using this function...
It this example, we can show the earliest date using this function...
=TEXT(MIN(B:B),"DD-MMM-YYYY")
...and the latest date function would be...
=TEXT(MAX(B:B),"DD-MMM-YYYY")
To merge / combine these two functions into one cell you could use below formula...
="From " & TEXT(MIN(B:B),"DD-MMM-YYYY")&" to "&TEXT(MAX(B:B),"DD-MMM-YYYY")
or, since the formula in this tip is also located in column B, using the B:B reference would cause a Circular reference error. Instead, we can use...
="From " & TEXT(MIN(B5:B10000),"DD-MMM-YYYY")&" to "&TEXT(MAX(B:B),"DD-MMM-YYYY")
... to get this result...
From 10-Jan-2014 to 25-Jul-2014
 |
| Dynamic Date Range |
Now, whenever this report is updated with new data and dates, the report title will automatically update the date range.
0 comments:
Post a Comment