Calculated dates

A date is typically displayed as a static number or a range, but this formula enables you to display specific dates like "the first Monday of the month" or "every second Wednesday."

1. 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.
2. 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.
3. Click the Structure tab and click value in the common section under the Attributes tab.
4. Click the round green + (Add Expression) icon in the Formula column. The Expression dialogue will appear.
5. Click the ellipsis (...) to open the Formula Editor dialog box.
6. Select Date/Time from the Category drop-down box.
7. Double-click the DATEVALUE item in the list on the left.
8. 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
   • 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.
9. Click Close to exit the Expression dialog box.
10. 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.

=DATEVALUE(DATE(YEAR(NOW());MONTH(NOW());1))

=DATEVALUE(DATE(YEAR(NOW());MONTH(NOW());DAY(NOW())-WEEKDAY(Now();2)))

=DATEVALUE(DATE(YEAR(NOW());MONTH(NOW());DAY(NOW())-WEEKDAY(Now())+7))

=NOW()

=TODAY()

=DATEVALUE(DATE(YEAR(NOW());MONTH(NOW());DAY(NOW()-1)))