by Dealing with dates is a standard action, especially when it comes to user-facing applications. Today we’ll focus on the DateAdd function and how we can use it in Power App.
Usage
It follows a simple pattern.
- Date/Time
- Value to add
- Unit
Let’s break them down individually before we go to the examples.
Date/Time
Since we’re manipulating dates, the first parameter should be the date itself that we want to change. Notice that we can have the date and time or only the date to add.
Value to add
The second parameter is a numeric value that we’ll be adding to the date (last parameter) based on the unit (following parameter).
It’s important to note that the value can be negative, meaning we’ll subtract units to the date.
Finally, this number is an integer value, meaning that 3 and 3.14 are considered the same thing.
Unit
The units have quite a range since you can choose between Milliseconds, Seconds, Minutes, Hours, Days, Months, Quarters, or Years.
You should insert the values exactly as you see them above. However, Power App is case sensitive in the DateAdd function, so “days” will error.
Examples
Let’s check some examples, starting with today’s date.
Now()
will return:
04/11/2021 09:50
If we add minutes:
DateAdd(Now(),2,Minutes)
will return:
04/11/2021 09:52
We can decrease the minutes by making the number negative:
DateAdd(Now(),-2,Minutes)
will return:
04/11/2021 09:48
The quarters are also an interesting parameter:
DateAdd(Now(),2,Quarters)
will return:
04/05/2022 09:50
Finally, let’s add a couple of years to see what we get:
DateAdd(Now(),2,Years)
will return:
04/11/2023 09:48
Notice that the calculation returns what we expect, although there are leap years, daylight savings, etc.
Limitations
It’s not a big deal, but there’s no week in the list of choices. Of course, you can efficiently perform the calculation by adding seven, but since we have “quarters” as an option, the “week” would also make sense.
Recommendations:
Here are some things to keep in mind.
Timezones can influence results.
Please be very carrell with the timezone that you’re performing the calculations. Depending on the timezone, adding one hour can return the same value or two hours in the future. Convert the date to the timezone and then perform the operations to ensure that you always have the correct results.
Never calculate yourself
I repeat this frequently, so I’ll warn here as well. NEVER try to calculate dates or perform operations manually. Instead, ALWAYS use system functions to perform operations. Dates are the most complex things to calculate due to timezones, daylight savings, etc., so use the system functions to do the calculation. For example, if you want to calculate the difference between two dates, use the DateDiff function.
Trust me. If you try to calculate, you’ll always make mistakes, so use the system functions to do all the work for you.
Milliseconds are overkill
In my opinion, adding milliseconds is overkill. Power Apps takes a few milliseconds to parse the information, render the UI and return data to the user. Although all of this is fast, adding milliseconds is not helpful since things change from one call to another.
If you have an excellent example of the usage of adding “milliseconds,” please let me know.
Always use the most significant unit.
Although you can use 60 seconds to define one minute. For example:
DateAdd(Now(),1,Minutes)
is the same as
DateAdd(Now(),60,Seconds)
Another advantage is that you don’t need to do complex calculations, like calculating the number of days in a month to add to the date. Instead, you can add one month, and you’re sure that your results are always correct.
Don’t nest it.
Although there are cases where it makes sense to nest the “DateAdd” function, I would strongly advise against it.
Add days and then minutes, for example
Localization
Please note that formulas may have localization-based differences. For example, you should write “DateAdd” with "," separating each of the arguments, but if your regional settings are set to Portugal, you should use ";" instead.
Sources:
DateAdd, DateDiff, and TimeZoneOffset functions in Power Apps
Back to the Power Apps Function Reference
Photo by Charisse Kenion on Unsplash
