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.
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.
As a starting point, a scatter plot is created, showing one marker per project.
- Right-click the Y-axis selector to open the pop-up menu.
- Select Custom Expression.
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.
- Click OK.
Calculating time periods between time points and today
Sometimes, you are interested in the time period between a certain time point and today. 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.