Absolute and Relative Dates
Drag a time element or a set of time elements from the Reporting pane to selected rows or columns in the worksheet. Refine these time elements and automate periodic reporting by specifying whether the time elements are absolute or relative to the report date, which is the date your report is based on.
An absolute date is always the same, regardless of any changes to your report date. A relative date is updated when you change your report date.
For example, you design a report that has a report date of October 1, 2014. Next, you apply time elements to show that the account data is for August and September 2014. If these are absolute dates, the time elements always show as August 2014 and September 2014, even if you now change the report date to December 1, 2014.
If you select the option to make new time elements relative to the report date in the workbook properties, the time elements reflect as -2 Months, -1 Month, and so on. In this case, if you change your report date to December 1, 2014, the time elements dynamically pull in data from October 2014 and November 2014.
You can set up a report so that you can easily reuse it across succeeding time periods. For example, if you generate a particular type of report every month or every quarter, set it up with relative dates. All dates are relative from the report date set for the workbook. To update the workbook for the new period, simply change the report date and then refresh the report. OfficeConnect pulls the data for the specified dates relative to the new report date.
Note: With relative dates, you can define a label in the worksheet to show the fiscal period, for example, Q3-FY14. See Define Report Labels.
You might need absolute dates for certain reports, relative dates for other reports, and a mixture of absolute and relative dates for still others. In this section, you’ll learn how to change the date type for a new or existing report, for an existing time element, and for new ones.
Specify the Report Date and Default Date Type
When you create a workbook, the default indicates that time elements are relative to the report date. To make most time elements absolute, clear Make new time elements relative to Report Date. You can make exceptions to the default. Also, you can have a mix of absolute and relative dates in your report.
The date type changes for any new time elements you apply to the reports in a workbook. Any existing time elements remain unchanged. To change existing time elements, see Apply Absolute and Relative Dates in this article.
You can only have one report date in a workbook, even across multiple worksheets in the workbook.
To change the report date or date type in an existing workbook:
From Book group, select Workbook Properties.
In the Report date field, enter or select the report date you want.
By default, this is the date the report is created. The report date is particularly important if you’re using relative dates. Set the report date before changing any time elements from absolute to relative.
Select or clear Make new time elements relative to the Report Date.
The default indicates that time elements are relative to the report date. Make your selection based on which date type you expect to use the most in your report.
Apply Absolute and Relative Dates
You can apply both absolute and relative time elements in the same report. You can set the absolute date type and apply to the time elements as required. Similarly, you can change the date type to relative and apply to the time elements so they are relative to the report date.
To change the date type for an individual time element, delete the existing time element from the report and reapply it to adopt the date type set in the workbook properties.
Important: Before changing any time elements from absolute to relative, set the report date in workbook properties.
To change the date type of an existing time element:
- Select the row, column, or cell containing the existing time element. For example, if the time element applies to a cell, select the cell in the grid.
- From the Reporting pane, select the Review tab
The Review pane shows the metadata including the time element for the selected row, column, or cell.
- Right-click the metadata line for the time element for the row, column, or cell and select Switch to Absolute or Switch to Relative.
For example, if the time element applies to a cell, right-click the meta data line for Time under Cell.
- Select Refresh to see the results of your time change.