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
TIMEZONEOFFSET Function
Date & Time Parsing Functions
YEAR Function
MONTH Function
DAY 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 Format | Text Format | Result |
| 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
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()
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
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
| Formula | Result |
| 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
| Formula | Result |
| Clock.AmPm() | [ “AM”, “PM” ] |
| Clock.AmPmShort() | [ “A”, “P” ] |
| Clock.IsClock24() | FALSE |
Did You Enjoy This Article? 😺
Subscribe to get new Power Apps articles sent to your inbox each week for FREE
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.
I like having all this in one place, and where there are real-world examples. You should be writing the docs on Microsoft.
URLs are broken.
Nuno,
Which URLs do not work? It would be more helpful if you can tell me how to reproduce the issue.
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)”,
“”
)
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
Rachel,
Great question! I think its going to be a little bit long to describe in the comments section here. So I think the best way to communicate how to do this would be to watch this video by my good friend April Dunham (https://www.youtube.com/watch?v=zroxwXpVhHI). She made a ‘desk book reservation system’ and shows how it was built in the video. You can even download a copy of the app to inspect it.
I am considering writing a ‘booking system’ app for my own blog. If you have any other requirements let me know and I’ll see what features I can include when I get to writing it.
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!
Jeremy,
Would you please repost this under the related article “Everything You Need To Know About Power Apps Patch Forms.” I can’t manually move this post and I’d like to try to keep the chats “on-topic” for each article.
Thanks in advance. I’ll await your reply.
Awesome resource. – Thanks, Matthew!
Jason,
Thanks for the kind words!
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
Bujj,
The solution is:
Text(DateDiff(StartTime, EndTime, Hours))&”:”Text(Mod(DateDiff(StartTime, EndTime, Minutes),60))
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!
Luis,
I’m glad you liked the article. Suggest you visit the community forums for the answer to your flow question:
https://powerusers.microsoft.com/t5/Power-Apps-Community/ct-p/PowerApps1
Thank you very much for your post and time invest, really works for me.
Juan,
You’re welcome 🙂
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,
I don’t understand your question.
I have attached a image for this column the user have to select only 1st and 16th date of the month only
Sreevani,
I think this would be better accomplished using 3 dropdowns instead of a DatePicker. Dropdowns can use a set of defined values. In a datepicker there are way too many invalid dates.
* Month Dropdown (Jan, Feb, Mar… Dec)
* Day Dropdown (1, 16)
* Year Dropdown (2022, 2021, 2020…)
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’)
Lalit,
I don’t think we are allowed to use the IN operator to search dates. Suggest you try using a datepicker to find a specific date or two datepickers to define a range of dates to search.
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…
Lalit,
Searching a date value that has a data type of text is going to be problematic. SharePoint delegation does not support the Search function.
Date pickers can be used to filter a date range. The search does not have to be in a single “specific” date.
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”.
Nikil,
That is a difficult date format to do data validation on. Instead, I suggest you create a datepicker to input the date and some dropdowns to enter the time. Then convert the selections to the format you need when the user clicks submit.
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?!
Same issue here.
Can we have a calendar in gallery like the one in Edit form which can have hour and minutes to choose for users?
Jasmine,
Yes, you can build a gallery to select hours & minutes.
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
Seema,
I suggest you convert time to UTC like this and then store it in SQL.:
DateAdd(Now(), TimeZoneOffset(), Minutes)
Then, when showing time in the app display it back in the user’s timezone like this
DateAdd(StartTime, −TimeZoneOffset(StartTime), Minutes)
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
Hi Martin,
Set DatePicker control properties as below:-
DefaultDate: Parent.Default
Format: “dd-mm-yyyy” or “dd/mm/yyyy”.
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.
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.
Do I have to calculate to convert the time to milliseconds?
Eric,
Can you give additional context please?
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.
Eric,
My thought would be:
DateDiff(Date(1970, 1, 0), Date(2023, 7, 30), TimeUnit.Milliseconds)
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
Nigel,
I think I need a couple of conversion examples to understand the problem you’ve posed.