The formula described here will provide a calculated date in a report. Typically you would display a date as a static number or a range, but the process explained below will enable you to display specific dates like "the first Monday of the month" or "every second Wednesday."
- Open an existing report or create a new report and establish a data source and query, then drag your data-driven fields onto the canvas.
- Select (left click) the text field you want to print the calculated date in; if you do not have a text field dedicated to this task, create one now.
- Click the Structure tab and click value in the common section under the Attributes tab.
- Click the round green + (Add Expression) icon in the Formula column. The Expression dialogue will appear.
- Click the ellipsis (...) to open the Formula Editor dialog box.
- Select Date/Time from the Category drop-down box.
- Double-click the DATEVALUE item in the list on the left.
- Enter in your DATEVALUE formula, then click OK. For more information on DATEVALUE's parameters, see the OASIS reference page for DATEVALUE: http://www.oasis-open.org/committees/download.php/16826/openformula-spec-20060221.html#DATEVALUE and VALUE: http://www.oasis-open.org/committees/download.php/16826/openformula-spec-20060221.html#VALUE. Alternatively you can consult the examples below and modify them for your purposes.
- Click Close to exit the Expression dialog box.
- Click Preview and verify that your date values are properly calculated and formatted. You may have to adjust your query if it does not produce a testable result set.
Some common calculated date formulas: