Search Results

Calculating time periods

You can calculate differences between columns containing points of time to retrieve time periods. The result can be expressed in various units of time.

The function to use in the calculation is Datediff().

For example, the data table below contains start and completion dates for some projects, and you might be interested in how many days each project lasted. Then you can calculate the length of each time period between the start date and the completion date.
Data table with two time columns
As a start, a scatter plot is created with one marker per project.


  1. Right-click the Y-axis selector to open the pop-up menu.
  2. Select Custom Expression.
  3. In the Custom Expression dialog, enter the expression DateDiff("day",[Start date],[Completion date]).
    The expression states from which date and to which date the period to calculate spans, and "day" defines which time unit to use for the result.
  4. Click OK.
    The Y-axis expression now shows how many days each project lasted.

Calculating time periods between time points and today

Sometimes, the time period between a certain time and today is of interest. For example, if a data column contains dates of birth for individuals, their current ages can be calculated. The DateTimeNow()function retrieves the current time in an expression.

In another example, the DateTimeNow() function is used to identify invoices that are urgent to pay, because their due dates are passed. The data used is shown below.

If you apply the expression DateDiff("day",DateTimeNow(),[Due date]), negative differences indicate passed due dates.

The expression is used in the scatter plot below on the 6th of May 2015, and two invoices, I-123 and I-126, with passed due dates are found.