DateDiff

calculates the difference between two dates

The DateDiff function in Power FX calculates the difference between two dates. It returns the number of days, months, or years between the two specified dates.

Syntax

The basic syntax in Power FX is:

DateDiff(ThisItem.ColumnStartDate, ThisItem.ColumnEndDate, TimeUnit.[Unit])

However

In AppAlloy, your DateTime data is ISO 8601 formatted which is different from the format of DateTime that works with PowerFX.

Therefore, use DateValue or DateTimeValueto convert the DateTime data.

Then, the complete syntax is:

• If data includes Date only

DateDiff(DateValue(ThisItem.ColumnStartDate), DateValue(ThisItem.ColumnEndDate), TimeUnit.[Unit]))

• if data includes Date and Time

DateDiff(DateTimeValue(ThisItem.ColumnStartDate), DateTimeValue(ThisItem.ColumnEndDate), TimeUnit.[Unit]))

In which:

ThisItem.ColumnStartDate: The column contains the data of the start date. If the heading has any special characters, keep the headings in brackets. For example: ThisItem.'Start Date'.

ThisItem.ColumnEndDate: The column contains the data of the end date. If the headings has any special characters, keep the headings in brackets. For example: ThisItem.'End Date'.

TimeUnit.[Unit]: (Optional) The type of Units to subtract including TimeUnit.Milliseconds, TimeUnit.Seconds, TimeUnit.Minutes, TimeUnit.Hours, TimeUnit.Days, TimeUnit.Months, TimeUnit.Quarters, or TimeUnit.Years. If not specified, TimeUnit.Days are used.

Example

Table

Let’s say you have a table to track your Projects with columns of StartDate and EndDate.

Requirement

Calculate how long it has taken to complete each task, unit "day(s)".

Formula

DateDiff(DateTimeValue(ThisItem.'Start Date'), DateTimeValue(ThisItem.'End Date'),TimeUnit.Days)

Result

If the formula and data are correct, you can see the value of column when you set to view it in the app.