| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
Difference between revisions of "Date Slicer"
| Line 34: | Line 34: | ||
So let's change the slicer range to also include B3 (which holds our selection type) and click OK. | So let's change the slicer range to also include B3 (which holds our selection type) and click OK. | ||
| − | [[Image:ds8.png| | + | [[Image:ds8.png|350px|centre]] |
Set B3 to M and now when you click the drop-down box you will be be able to pick only months between the range specified. | Set B3 to M and now when you click the drop-down box you will be be able to pick only months between the range specified. | ||
| − | [[Image:ds5.png| | + | [[Image:ds5.png|350px|centre]] |
Similarly change the type to D for day and Y for Y: | Similarly change the type to D for day and Y for Y: | ||
| − | [[Image:ds6.png| | + | [[Image:ds6.png|350px|left]] [[Image:ds7.png|350px|right]] |
Revision as of 14:54, 19 September 2012
Date Slicers can be based on an Excel range, or direct from SQL. This example shows you how to use it from Excel, but a SQL statement which returns the same result set format can also be used.
The first thing to do is to set up two Excel cells to hold the date range. We have a start date and an end date in cell locations B1 and B2 as below:
Next you insert an Excel slicer from the Slicer tab
You will see Insert Slicer Window displayed.
Set the Slicer Range to point to the Start Date and End Date (cells B1:B2 in this example)
Select the Date Slicer type
Check the box to Update Range With Selection and enter a cell location. This will be where the date selected will be held. The date selected is displayed as the serial number equivalent so that the user can then choose to format the date as they need.
Click OK to exit.
Click the drop-down on the slicer and XLCubed will display a calendar control which can be navigated between the specified start and end dates.
Click a date to select it and it will be written as the Excel serial number equivalent to the cell location defined in Update Range With Selection.
You can then choose to format this by using the Excel TEXT function eg =TEXT(E1,"dd-mm-yyyy").
You can also include a third cell in your Slicer Range as Selection Type eg Day, Month or Year. Valid selections are D, M or Y. Day is taken to be the default selection type and is the default display mode if no selection type if specified.
So let's change the slicer range to also include B3 (which holds our selection type) and click OK.
Set B3 to M and now when you click the drop-down box you will be be able to pick only months between the range specified.
Similarly change the type to D for day and Y for Y:
