Mastering Date and Time Calculations in Power Apps

Dates and times are at the heart of nearly every business application. In Power Apps, manipulating dates can feel tricky because of time zones, locale-specific formats, and the difference between date-only and date-time values. This guide will walk you through the essential date and time functions using a realistic project management scenario. You'll learn how to construct, parse, format, and calculate dates and times, handle time zone offsets, and avoid common pitfalls.

Scenario: Milestone Dashboard

Imagine you have a SharePoint list called Milestones with these columns:

We'll build formulas that display today's date, calculate days remaining, highlight overdue items, and convert UTC stored dates to local time.

Core Date and Time Functions

Today and Now

Today() returns the current date only (with time set to midnight). Now() returns the current date and time.

Today()   // 2026-06-03 (depending on current date)
Now()     // 2026-06-03 14:30:00 (current local date and time)

Use Today() when you need only the date (e.g., comparing due dates) and Now() when you also care about the exact time (e.g., logging a timestamp).

Building Dates and Times

Date(Year, Month, Day) creates a date value from individual numeric components.

Time(Hour, Minute, Second) creates a time value.

Combine them with the + operator to get a full datetime.

Date(2026, 7, 4)                           // 2026-07-04
Time(14, 30, 0)                            // 2:30 PM
Date(2026, 7, 4) + Time(14, 30, 0)        // 2026-07-04 2:30 PM

In our milestone app, we might create a deadline reminder datetime:

DateAdd(StartDate, 7) + Time(9, 0, 0)     // StartDate + 7 days at 9:00 AM

Parsing Date and Time Strings

When you receive dates as text—for example, from an external API or a user input—use DateValue, TimeValue, and DateTimeValue.

DateValue("2026-07-04")                  // 2026-07-04
TimeValue("14:30")                        // 2:30 PM
DateTimeValue("2026-07-04 14:30:00")      // 2026-07-04 2:30 PM

These functions automatically adapt to the current user's language and region, but you can also specify a language code:

DateValue("04/07/2026", "fr")            // July 4, 2026 (French order: day/month/year)

In our milestone list, we could parse a user-supplied date string into a date column update.

Formatting Dates with Text

The Text function is the go-to for formatting dates into display strings. It accepts a format string or an enum constant.

Text(Today(), "dddd, mmmm d, yyyy")    // "Wednesday, June 3, 2026"
Text(Now(), ShortDateTime)              // localized short date + time
Text(Now(), "yyyy-MM-dd")               // "2026-06-03"

Common format codes:

Use Text to display due dates in a gallery label:

Text(ThisItem.DueDate, "dddd, mmmm d")

Date Arithmetic

DateAdd and DateDiff

DateAdd(start, addition [, units]) adds a number of time units to a date. Negative numbers subtract.

DateDiff(start, end [, units]) returns the difference between two dates.

' Days until next milestone
DateDiff(Today(), DueDate, Days)

' Add 2 weeks to start date
DateAdd(StartDate, 14, Days)

' Subtract 3 months from DueDate
DateAdd(DueDate, -3, Months)

In our dashboard, we can show days remaining with conditional formatting:

If(
  Status = "Completed", RGBA(0,180,0,1),
  DueDate < Today(), RGBA(220,50,50,1),   // overdue
  DateDiff(Today(), DueDate, Days) <= 7, RGBA(255,180,0,1),  // due within a week
  RGBA(0,0,0,1)
)

EDate and EOMonth

EDate(date, months) shifts a date by a given number of months, keeping the same day (clamped if the day exceeds the target month).

EOMonth(date [, months]) returns the last day of the month for a given date, optionally offset by months.

EDate(Today(), 3)                // 3 months from now
EOMonth(Today())                  // last day of current month
EOMonth(Today(), -1)              // last day of previous month

Use EOMonth to calculate quarterly milestone deadlines:

EOMonth(StartDate, 3)            // End of quarter following start date

Time Zone Handling

Power Apps stores dates in UTC when the datasource (like Microsoft Dataverse) supports it. The user interface shows local time, but if you work with the raw values you often need to convert.

TimeZoneOffset() returns the number of minutes your local time is behind UTC. In my time zone (UTC-5), TimeZoneOffset() is -300.

To convert a local time to UTC (e.g., before saving to a UTC-aware column):

DateAdd(Now(), TimeZoneOffset(), Minutes)

To convert a UTC datetime from your datasource to local time for display:

DateAdd(UTCTime, -TimeZoneOffset(UTCTime), Minutes)

In our milestone list, if ActualFinish is stored as a date-only field, no time zone conversion is needed. But if we had a LastModified datetime column (UTC), we'd use the conversion above.

Extracting Date Components

Use Year, Month, Day, Hour, Minute, Second, and Weekday to pull out parts of a date.

Year(DueDate)           // e.g. 2026
Month(DueDate)          // 7 (for July)
Day(DueDate)            // 10
Weekday(DueDate)        // 6 (Saturday; default Sunday=1)
Weekday(DueDate, StartOfWeek.MondayZero) // 5 (if Monday is 0)

You might combine these to build a custom date key for sorting:

Year(DueDate) * 10000 + Month(DueDate) * 100 + Day(DueDate)

Performance and Delegation

Most date functions are delegable in SharePoint and Dataverse when used in filter formulas with simple comparisons (e.g., Filter(Milestones, DueDate = Date(2026,6,10))). However, as soon as you apply a calculation like DateAdd inside a filter, delegation may be lost.

Optimization tip: Pre‑calculate date boundaries outside the delegation context, then use static values inside the filter.

Set(varNextWeek, DateAdd(Today(), 7, Days));
ClearCollect(DueSoon, Filter(Milestones, DueDate <= varNextWeek))

This keeps the filter delegable.

Common Mistakes

1. Locale‑dependent parsing – DateValue("07/04/2026") can be interpreted as July 4 or April 7 depending on user language. Always use explicit formats with the Text function or specify a language code when parsing.

2. Time zone confusion – When displaying a UTC datetime without conversion, users may see unexpected times. Always convert to local time for display and to UTC for storage.

3. Date value comparison with Now() – Comparing a date‑only column (like DueDate) with Now() fails because Now() includes a time component. Use Today() for date‑only comparisons.

4. Adding months with DateAdd – DateAdd with months may not clamp the day correctly for month‑ends. Use EDate which follows the same logic as Excel.

5. Formatting strings – The Text function is case‑sensitive for format codes. Using "mmmm" works, but "MMMM" may not produce the expected result. Always use lowercase for minute mm and uppercase for month MM in the same code.

Troubleshooting

• Unexpected null values – If a date column is blank, functions like DateDiff will fail. Use IsBlank check first.

• Delegation warnings – In Power Apps Studio, hover over the function call; if it says “Warning: This function may not be delegable”, restructure the formula.

• Format not updating – Ensure the format string does not contain any extra spaces or invalid characters. Build the format string as a text constant.

Recommendation

Start by clearly defining which columns in your datasource are date‑only and which are datetime. Write all date‑manipulation logic in a single location (e.g., a collection or variable) rather than scattering it across controls. When you need to display dates to users, convert them to the local time zone and apply a consistent format using Text. Test your app across different user locales to catch parsing issues early.

References

• Matthew Devaney, All Power Apps Date & Time Functions (With Examples) – original article used as inspiration. https://www.matthewdevaney.com/all-power-apps-date-time-functions-with-examples/
• Microsoft Learn, Power Apps Date and Time Functions – for official documentation. https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-date-add
• Microsoft Learn, Text function – for all formatting codes. https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-text