PowerApps DateDiff function, the Microsoft Power Fx language is heavily influenced by the Excel formula language. The semantics of types, operators, and functions are as close to Excel as possible. If Excel fails to provide an answer, we turn to SQL.

In this blog post, we will learn how to use the PowerApps DateDiff function to returns the difference between 2 dates in specific time unit.

What is PowerApps DateDiff function?

PowerApps DateDiff function, is a Power Fx function that used to calculate the difference between two dates in unit time such as Milliseconds, Seconds, Minutes, Hours, Days, Months, Quarters, or Years.

The unit you specify will be the result it returns.

PowerApps DateDiff Syntax

DateDiff( StartDateTime, EndDateTime [, Units ] )

• DateDiff: the name of the function
• StartDateTime: the starting date/time value, its is required
• EndDateTime: the endingdate/time value, its is required
• Units: it is optional, is the interval to use when comparing dates and can be Milliseconds, Seconds, Minutes, Hours, Days, Months, Quarters, or Years. If not specified, Days are used.

DateDiff Return Value

The PowerApps DateDiff function returns a whole number that is the count of interval boundaries crossed between two dates.

PowerApps DateDiff datepicker

In these example we will learn how to calculate the DateDiff function using date picker control.

PowerApps DateDiff years

Years between = DateDiff(StartDate, EndDate,Years)

Example: retrieve the number of years between two dates

If date2 less than date 1 , it will return negative value

PowerApps DateDiff Months

Months between = DateDiff(StartDate, EndDate,Months)

It will return the difference between date1 and date2 in months and If date2 less than date 1 , it will return negative value.

PowerApps DateDiff Days

Dayes between = DateDiff(StartDate, EndDate,Days)
or 
Dayes between = DateDiff(StartDate, EndDate)

DateDiff(date1,date2,Days) 
or you can write
DateDiff(date1,date2) 

you can write the formula with out the days unit

It will return the difference between two dates in Days and If date2 less than date 1 , it will return negative value.

PowerApps DateDiff Quarters

Quarters between = DateDiff(StartDate, EndDate,Quarters)

it will return the difference between two dates in quarters.

PowerApps DateDiff time interval

PowerApps DateDiff Hours: DateDiff(StartDate, EndDate,Hours)
PowerApps DateDiff Minutes: DateDiff(StartDate, EndDate,Minutes)
PowerApps DateDiff Seconds: DateDiff(StartDate, EndDate,Seconds)
PowerApps DateDiff Milliseconds: DateDiff(StartDate, EndDate,Milliseconds)

PowerApps DateDiff Strings

We disscused how to calculate the difference between two dates in unit time using date picker controls, but how to use the DateDiff with strings?

Ex1:

DateDiff( Now(), DateValue("1/3/2022"),Months )

Ex2: We use the DateValue function that is used to convert a string into a Date value.

DateDiff(DateValue(TextInput1.Text),DateValue(TextInput2.Text),Months)

PowerApps DateDiff with current date

In this example, we will calculate the difference with current date today or now that =10/2/2022

The TODAY Function will return the current date, while the NOW Function will return the current date and time.

DateDiff(date1,Today())

Today time is 12:00:00 AM

DateDiff(date1,Now())

Now time is the current time

As we mentioned before if we didn’t write the unit , it will calculate the difference between the two days in days, so here the Today and Now function will return the same result

In the following examples the PowerApps DateDiff function Returns the difference between the system date and date1 “date picker control”

DateDiff(date1,Now(),Milliseconds) return the difference between date1 and now in Milliseconds
DateDiff(date1,Now(),Seconds) return the difference between date1 and now in Seconds
DateDiff(date1,Now(),Minutes) return the difference between date1 and now in Minutes
DateDiff(date1,Now(),Hours) return the difference between date1 and now in Hours
DateDiff(date1,Now(),Days)  return the difference between date1 and now in Seconds
DateDiff(date1,Now(),Months) return the difference between date1 and now in Months
DateDiff(date1,Now(),Quarters) return the difference between date1 and now in Quarters
DateDiff(date1,Now(),Years) return the difference between date1 and now in Years

Note: if you use the Today() function instead of now the time in Today is set to 12:00:00 AM but Now the time is the current time

Note: if date1 greater than Now it will return negative value

Why DateDiff returns negative value in PowerApps

If start_date is larger than end_date, the PowerApps DateDiff function will return a negative value as shown in the bellow example.

PowerApps Duration between two dates

As we mentioned before that the DateDiff function return the unit you specify, but what will we do to to get a duration of the total Days in Years, Months and Days.

We want to know the difference between two dates, not as a unit, but as a representation of the total Days in Years, Months, and Days to get a duration. To get that and ensure we’re honoring the calendar between the dates, we need to go through a couple of steps, starting with years and working backwards to months and then day.

Conclusion

The PowerApps DateDiff function is a simple function that you can use to calculate the time difference between two dates in PowerApps.

In this article, we have provided many examples for DateDiff function in Power BI to calculate:

• PowerApps DateDiff in Years
• PowerApps DateDiff in Quarter
• PowerApps date DateDiff in Months
• PowerApps date DateDiff in Weeks
• PowerApps date DateDiff in Days
• PowerApps date DateDiff in Hours
• PowerApps date DateDiff in Minutes
• PowerApps date DateDiff in Seconds
• PowerApps date DateDiff in milliseconds

See Also

• Nested Collection In PowerApps :3 Level
• PowerApps Parse JSON Example
• Show Internet Base Time In PowerApps |
• Canvas App Collections
• How To Overcome PowerApps Delegation Limit?
• How To Use PowerApps Value Function?
• Share PowerApps With External Users / Guest Users
• How to use DAX DAY Function in Power BI?
• How to use Date function in DAX Power BI?
• How to use Power BI DAX CALENDERAUTO?

Join us

• Subscribe to Power Platform Geeks.
• Register to Saudi Arabia Power Platform User Group.

Need Help

• Have a related question? Please ask it at deBUG.to Community.