All Power Apps Date & Time Functions (With Examples)

All Power Apps Date & Time Functions (With Examples)

Working with dates & times is one of the biggest challenges in Power Apps. Dealing with date formats, time-zones and date manipulation is even hard for experienced Power Apps developers. In this article I will list all of the Power Apps date & time functions and show examples of how to use them.

Table Of Contents:

Current Date & Time
TODAY Function
NOW Function

Type Conversion Functions
DATE Function
TIME Function
DATEVALUE Function
TIMEVALUE Function
DATETIMEVALUE Function
TEXT Function

Date & Time Manipulation Functions
DATEADD Function
DATEDIFF Function
EDATE Function
EOMONTH Function
TIMEZONEOFFSET Function

Date & Time Parsing Functions
YEAR Function
MONTH Function
DAY Function
WEEKDAY Function
WEEKNUM Function
ISOWEEKNUM Function
HOUR Function
MINUTE Function
SECOND Function

Logical Functions
ISTODAY Function

Date & Time Information Functions
CALENDAR Function
CLOCK Function




Current Date & Time Functions



Today Function



Purpose
Returns the current date


Syntax

Today()


Example
Assume the current date & time is January 15, 2021 5:00:00 PM.

Today()  // Result: January 15, 2021


Now Function



Purpose
Returns the current date and time


Syntax

Now()


Example
Assume the current date & time is January 15, 2021 5:00:00 PM.

Now()  // Result: January 15, 2021 5:00 PM




Type Conversion Functions



Date Function



Purpose
Creates a date from a year, month and day


Syntax

Date(year, month, day)


Arguments

year – number for the year

month – number for the month (January is 1, February is 2, March is 3… December is 12)

day – number for the day


Examples

Date(2021, 1, 15)  // Result: January 15, 2021
Date(2021, 9, 4)   // Result: September 4, 2021
Date(2018, 3, 11)  // Result: March 11, 2018


Time Function



Purpose
Creates a time from hours, minutes and seconds


Syntax

Time(hours, minutes, second)


Arguments

hour – number for the hour (12AM is 0, 1AM is 1, 2AM is 2… 11PM is 23)

minute – number for the minute

second – number for the second


Examples

Time(2, 30, 0)   // Result: 2:30 AM
Time(14, 30, 0)  // Result: 2:30 PM
Time(19, 15, 10) // Result: 7:15:10 PM



A Date & Time can be combined into a single DateTime value like this:

Date(2021, 1, 20) + Time(14, 30, 0)  // Result: January 20, 2021, 2:30 PM


DateValue Function



Purpose
Converts a date stored as text into a date data-type


Syntax

DateValue(string [,language])


Arguments

string – text string containing a date

language [optional] – two letter language code, defaults to current user’s language


Examples

DateValue("January 15, 2021")  // Result: January 15, 2021 
DateValue("01/15/2021")        // Result: January 15, 2021


TimeValue Function


Purpose
Converts a time stored as text into a time data-type


Syntax

TimeValue(string [,language])


Arguments

string – text string containing a time

language [optional] – two letter language code, defaults to current user’s language


Examples

TimeValue("2:00 PM") // Result: 2:00 AM
TimeValue("17:00")   // Result: 2:00 PM


DateTimeValue Function



Purpose
Converts a date & time stored as text into a time data-type


Syntax

DateTimeValue(string [,language])


Arguments

string – text string containing a datetime

language [optional] – two letter language code, defaults to current user’s language


Example

DateTimeValue("October 11, 2014 1:50:24 PM")  // Result: October 11, 201 1:50:24 PM


Text Function


Purpose
Applies a date format and changes the data-type to text


Syntax #1

Text(NumberOrDateTime, DateTimeFormatEnum [, ResultLanguageTag])


Arguments

NumberOrDateTime – text string containing a datetime

DateTimeFormatEnum – value belonging to the DateTimeFormat enum. See list below.

ResultLanguageTag [optional] – two letter language code, defaults to current user’s language


Example
Assume the current date & time is January 15, 2021 5:00:00 PM.

Text(Today(), "m/d/yyyy")  // Result: "1/15/2021"



Syntax #2

Text( NumberOrDateTime, CustomFormat [, ResultLanguageTag ] )


Arguments

NumberOrDateTime – text string containing a datetime

Custom Format – text string with date formatting code. See list below.

ResultLanguageTag [optional] – two letter language code, defaults to current user’s language


Example
Assume the current date & time is January 15, 2021 5:00:00 PM

Text(Today(), "m/d/yyyy")  // Result: "1/15/2021"



Syntax #3

Text(NumberOrDateTime)


Arguments

NumberOrDateTime – text string containing a datetime


Example
Assume the current date & time is January 15, 2021 5:00:00 PM.

Text(Today())  // Result: "1/15/2021"



Date and Time Formatting Codes
Use these formatting codes in the 2nd parameter of the Text function.

Enum FormatText FormatResult
LongDate“dddd, mmmm d, yyyy”“Friday, January 15, 2021”
LongDateTime“dddd, mmmm d, yyyy hh:mm:ss AM/PM”“Friday, January 15, 2021 5:00:00 PM”
LongDateTime24“dddd, mmmm d, yyyy hh:mm:ss”“Friday, January 15, 2021 17:00:00”
LongTime“hh:mm:ss AM/PM”“5:00:00 PM”
LongTime24“hh:mm:ss”“17:00:00”
ShortDate“m/d/yyyy”“1/15/2021”
ShortDateTime“m/d/yyyy hh:mm AM/PM”“1/15/2021 5:00 PM”
ShortDateTime24“m/d/yyyy hh:mm”“1/15/2021 17:00:00”
ShortTime“hh:mm AM/PM”“5:00 PM”
ShortTime24“hh:mm”“17:00”
UTC“2021-01-15T23:00:00.000Z”




Date & Time Manipulation Functions



DateAdd Function


Purpose
Adds a number or days to a date & time value. Can also add another time unit such as hours or months. If a negative number is supplied the number of time units will be subtracted.


Syntax

DateAdd(DateTime, Addition [, Units])


Arguments

DateTime – date and time value

Addition – number of days or other time units to add to the DateTime

Units [optional] – one of the following enum values: Years, Quarters, Months, Days, Hours, Minutes, Seconds or Milleseconds. Default units are days.


Examples
Assume the current date & time is January 15, 2021 5:00:00 PM.

DateAdd(Today(), 7)          // Result: January 22, 2021
DateAdd(Today(), 2, Months)  // Result: March 15, 2021
DateAdd(Today(), -1, Years)  // Result: January 15, 2020


DateDiff Function


Purpose
Finds the a number or days between a start date and an end date. Can also add another time unit (e.g. hours, months)


Syntax

DateDiff(StartDateTime, EndDateTime [, Units])


Arguments

StartDateTime – starting date and time value

EndDateTime – ending date and time value

Units [optional] – one of the following enum values: Years, Quarters, Months, Days, Hours, Minutes, Seconds orMilleseconds. Default units are days.


Examples
Assume the current date & time is January 15, 2021 5:00:00 PM.

DateDiff(Today(), Date(2021, 01, 20), Days)  // Result: 5 days
DateDiff(Date(2021, 01, 15)+Time(9, 0, 0), Today(), Hours)  // Result: 8 hours


EDate Function


Purpose
Adds a given number of months to a date. The day number remains the same unless the new value is beyond the end of the month.

Syntax

EDate(StartDateTime, EndDateTime [, Units])


Arguments

Date – starting date and time value

Months – months to add or subtract from the date.



Examples
Assumes the current date is June 15, 2023.

EDate(Today(), 4)   // Result: October 15, 2023
EDate(Today(), -2)  // Result: April 15, 2023
EDate(Date(2023, 05, 31), 1)   // Result: June 30, 2023


EOMonth Function


Purpose
Returns the last day of the month for a given date.

Syntax

EOMonth(Date [, Months])


Arguments

Date – starting date and time value

Months [optional] – ending date and time value



Examples
Assume the current date is January 1, 2024.

EOMonth(Today())       // Result: January 31, 2024
EOMonth(Today(), 2)    // Result: March 31, 2024
EOMonth(Today(), -1)   // Result: December 31, 2023


TimeZoneOffset Function


Purpose
Returns the number of minutes between the user’s local time and Universal Co-ordinated Time (UTC)


Syntax

TimeZoneOffset()


Examples
Converts the user’s local time to UTC. Assume the user’s local current date & time is January 15, 2021 5:00:00 PM

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

// Result: January 15, 11:00PM



Converts UTC to the user’s local time. Assume the current UTC date & time is January 15, 2021 11:00:00 PM

DateAdd(
    StartTime,
    −TimeZoneOffset(StartTime),
    Minutes
)

// Result: January 15, 5:00PM





Date & Time Parsing Functions



Year Function, Month Function, Day Function, WeekNum Function, ISOWeekNum Function, Hour Function, Minute Function, Second Function



Purpose
Extracts an single part of the date & time value


Syntax

Year()

Month()

Day()

Weekday()

WeekNum()

ISOWeekNumber()

Hour()

Minute()

Second()


Examples
Assume the current date & time is January 15, 2021 5:00:00 PM.

Year(Now())        // Result: 2021
Month(Now())       // Result: 1
Day(Now())         // Result: 15
Weekday(Now())     // Result: 3
WeekNum(Now())     // Result: 3
ISOWeekNum(Now())  // Result: 2
Hour(Now())        // Result: 17
Minute(Now())      // Result: 0
Second(Now())      // Result: 0




Logical Functions



IsToday Function


Purpose
Checks whether a date & time value is within the current day and returns a true/false value.


Syntax

IsToday(DateTime)


Arguments

DateTime – a date & time value to compare


Examples
Assume the user’s local current date & time is January 15, 2021 5:00:00 PM.

IsToday(Date(2021, 1, 15)  // Result: true
IsToday(Date(2021, 1, 22)  // Result: false




Date & Time Information Functions


Calendar Function


Purpose
Returns calendar information for the user’s current locale.


Syntax

Calendar.MonthsLong()

Calendar.MonthsShort()

Calendar.WeekdaysLong()

Calendar.WeekdaysShort()


Examples

FormulaResult
Calendar.MonthsLong()[ “January”, “February”, “March”, “April”, “May”, “June”, “July”, “August”, “September”, “October”, “November”, “December” ]
Calendar.MonthsShort()[ “Jan”, “Feb”, “Mar”, “Apr”, “May”, “Jun”, “Jul”, “Aug”, “Sep”, “Oct”, “Nov”, “Dec” ]
Calendar.WeekdaysLong()[ “Sunday”, “Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday”, “Saturday” ]
Calendar.WeekdaysShort()[ “Sun”, “Mon”, “Tue”, “Wed”, “Thu”, “Fri”, “Sat” ]


Clock Function


Purpose
Returns clock information for the user’s current locale.


Syntax

Clock.AmPm()

Clock.AmPmShort()

Clock.IsClock24()


Examples

FormulaResult
Clock.AmPm()[ “AM”, “PM” ]
Clock.AmPmShort()[ “A”, “P” ]
Clock.IsClock24()FALSE





Questions?

If you have any questions or feedback about All Power Apps Date & Time Functions (With Examples) please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.

Matthew Devaney

Subscribe
Notify of
guest

46 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Valerie Schaffer
Valerie Schaffer
2 years ago

I like having all this in one place, and where there are real-world examples. You should be writing the docs on Microsoft.

Nuno Nougiera
Nuno Nougiera
2 years ago

URLs are broken.

Minte
Minte
2 years ago

Thank you, this is a comprehensive documentation to check for date and time functions.

I wish you have added an example about an age calculation as part of the examples. Last week I had to calculate an age with Year and months and used the below formula. Look forward to hear from you if there is a shorter version to the below formula.
If(
!IsBlank(varRecord.DOB),
If(
DateDiff(
Today(),
Date(
Year(Now()),
Month(lbl_DOB.Text),
Day(lbl_DOB.Text)
)
) Month(lbl_DOB.Text),
Month(Today()) – Month(lbl_DOB.Text),
Month(Today()) = Month(lbl_DOB.Text) && (Day(Today()) >= Day(lbl_DOB.Text)),
“0”,
Month(Today()) + 12 – Month(lbl_DOB.Text)
) & ” month(s)”,
“”
)

Rachel Irabor
Rachel Irabor
2 years ago

Hi Matthew, started reading your articles recently and I am loving them thank you very much.

I don’t know if I am explaining this well, but I am trying to create an app using power apps and I need help with date manipulation.

How can I set up a particular date and time a service will be available and the date it would end without being able to bypass it example the date a room will be available in a hotel and the amount of days the room will be available and can not exceed that time. Thank you

Jeremy Arnold
Jeremy Arnold
2 years ago

Hi Matthew, your articles are great. I’m using the one on “Edit A Record With A Patch Form” and am running into some issues with dates, radio buttons, and people. Everything is fine until I create the submit button. It then breaks my gallery and a couple of labels on my submit form. The purpose of the app is for someone to review a procedure and say whether it is accurate or not. So I’m just passing the record variable through for the document number and title into a label. I want the Radio to default to blank, the reviewer to be auto filled to current user(Label Text is: User().FullName), and the date to be auto filled(Default date is Today()).
The doc number and title are text in SP, Radio is a Choice, Date is Date/Time, and Reviewer is Person or Group.

Here is the code in the submit button:
// submit the form data
Set( //<– new code
  varCurrentRecord,
  Patch(
    ‘Finishing LOTO Updates’,
    varCurrentRecord, // <– new code
    {
      DocumentNumber: GalDocNumber.Text,
      DocumentTitle: GalDocName.Text,
      ProcedureIsAccurate: Radio1.Selected.Value,
      Reviewer: ‘Current User_1’.Text,
      DateofLastReview: DatePicker1.SelectedDate
    }
  )
)

What am I doing wrong? Thanks!

Jason Roberts
Jason Roberts
1 year ago

Awesome resource. – Thanks, Matthew!

Bujjibabu KJ
Bujjibabu KJ
1 year ago

Hi
I have 3 Labels.
Label1 -StartTime
Label2-EndTime
Label3- Date Diff (Hours : Minutes)
Label1&2 I have used – Text(Now(), “[$-en-US]mm/ddd/yyyy hh:mm AM/PM”)
request you help me to achieve DateDiff in Hours : Minutes

Thanks & Regards
Bujjibabu KJ

Time Diff function in PowearApps.JPG
Luis
Luis
1 year ago

Great article! THANS!
Ihave an issue trying to inform the Form Submission time into email body, sent by my Flow. The submission date/time of my form, loaded in my SP list, looks ok, is the real date time value, BUT I can’t show it correctly in my email body. Sometimes is correct, most times is 2 hours back… What am I doing wrong? Why is it variable?
I’ve tried to use the form value (submission time), and also directly the sharepoint list field…..all in same Flow, after loading my SP list.
Any help will be appreciated.
Thanks!

Juan Elí Arroto
Juan Elí Arroto
1 year ago

Thank you very much for your post and time invest, really works for me.

sreevani
sreevani
1 year ago

Hi
I have date column in which i have to display a specific date only
it is like the 1st or the 16th of the month only should be selected
Can you help me with this.

Thanks & Regards,
Sreevani.

sreevani
sreevani
1 year ago

I have attached a image for this column the user have to select only 1st and 16th date of the month only

date.png
Lalit
Lalit
1 year ago

Hi Matthew, I am Trying to capture Date from Excel which is in format of dd-mm-yy , but its getting Error Message, Error screenshot attached please have a look.

I am Searching Title14 text in Excel in column PartCode if match then its need to capture value of column End Support.

LookUp(EOSupport,Title14.Text in PartCode,’End Support’)

DateErrorCapture.PNG
Lalit
Lalit
1 year ago

I am not searching specific date…so can’t use date picker for searching…ok let me brief about the requirement…
1)EOSuport is my Excel Table
2)Partcode & End Support are My columns in EOSuport excel Table.
3)Title14 is my Text box in Powerapp.

In lookup Farmula I am searching Title14 text box text in partcode column in EODuport excel table and if this match then need to show the value of same row of column End support to another text box I.e TDate1 in powerapp….
End support values are in Date format which is in dd-mm-yy…when I put this formula in another text box TDate1 it’s getting date format error same error screenshot attached in my previous comment…

Nikil
Nikil
1 year ago

Dear @matthew,

Could you please help me with the below request,

I have created a TEXT INPUT box, users will update the details below format,
27/Dec/22 9:30 AM
If the user updated the wrong format needs show the error message before submitting the form.
In my form needs to update multiple dates and time fields, and users will copy and paste the details, hence we used the CARD “TEXT INPUT”.

Justin
Justin
1 year ago

Great article.

One area I am really struggling with is getting UK date formats to show correctly on Windows based browsers? For some reason everything previews correctly and works perfectly on Mac and iOs but never works correctly on Windows based browsers?!

Cindy
Cindy
9 months ago
Reply to  Justin

Same issue here.

Jasmine
Jasmine
1 year ago

Can we have a calendar in gallery like the one in Edit form which can have hour and minutes to choose for users?

Seema
Seema
1 year ago

Scenario : I have a Roster where a manager will submit the shift timings of their employees.
We have Managers across all regions example , Asian, European, EST, PST timezones.
Right now they all are entering the employee shift timings in PST timings .

Requirement : we want to them to be able to submit the shift timings of their employees in their respective timezones from Powerapps. So, how can we store this data in a common timing ( like PST) in backend. my backend database in Azure SQL ?

Please help me ! Thanks lot in advance

Martin Leube
Martin Leube
11 months ago

Hello Matthew, great documentation. I am using Text(date,ShortDate) to display dates and I’d like to show the date format (e.g. “mm/dd/yyyy” or “dd-mm-yyyy” etc.) in the tooltip. How would I do that? Thanks,
Martin

Smruti Wagh
Smruti Wagh
8 months ago
Reply to  Martin Leube

Hi Martin,

Set DatePicker control properties as below:-
DefaultDate: Parent.Default
Format: “dd-mm-yyyy” or “dd/mm/yyyy”.

Last edited 8 months ago by Smruti Wagh
Martin Leube
Martin Leube
7 months ago
Reply to  Smruti Wagh

Hello Smruti, thanks for your suggestions. I have nowhere a DatePicker, all are calculated dates stored in Dataverse date-only columns. I show them in text labels. People in different regions (want to) see them in different formats but in order to avoid misunderstandings I want to display the format in the tooltip.

Simeon
Simeon
10 months ago

This is strange .. DateAdd(Today(), 2, Months) no longer works. Its now changed to this … DateAdd(Today(), 2, TimeUnit.Months ). Don’t know when this started happening.

Eric
Eric
7 months ago

Do I have to calculate to convert the time to milliseconds?

Eric
Eric
7 months ago

I wanted to be able to get the current time in milliseconds (time since Jan 1, 1970 I think). I think I came across how by accident since posting, but basically I want the equivelent of using Text(“7/30/2023”,Milliseconds) which is not supported. Thanks Matthew, love you content.

Nigel
Nigel
7 months ago

Hi Matthew

What about converting ISO8601 Dates and Times to HH:mm ?
I seem to remember SQL Server stores Dates in ISO8601 format.
However they are normally displayed as HH:mm (or date equivalents)
Regards
Nigel

Alex
Alex
5 months ago

I am trying to force the language conversion to French for the Calendar.WeekdaysShort() function, but it is not working.
I have this under the Items property in my gallery. It resolves, but the output remains in English:
ForAll(Calendar.WeekdaysShort(),Text(Value,””,”fr”))

I also tried these 2 things:

Leaving it as Calendar.WeekdaysShort() in the gallery and adjusting the text label only with Text (ThisItem.Value,””,”fr”), which has the same output – Resolves but remains in English.Text(Value,”[$-fr]ddd”), which again has the same output – resolves, but remains in English.From your understanding, this is incorrectly being used? Or is there a bug to report to Microsoft?

Thank you.

Last edited 5 months ago by Alex
Shayan Iqbal
Shayan Iqbal
5 months ago

Hi Matthew,
I am looking for a formula where in a column value which is of “lookup type” when selected displays the current time on a tile of gallery display of a canvas app.

I am using a label as input. Using the below formula but there is some warning error:

If(ThisItem.Status.Value = “Off Duty”, (Text(Today(), “hh:mm AM/PM”)), false) it is expecting a true or false.

Regards,
Shayan Iqbal.