In this Power automate tutorial, we will discuss Power automate date functions. And also we discuss the below points:
- List of power automate date functions
- Power automate date format dd/mm/yyyy
- Power automate date format mm/dd/yyyy
- Power automate date format yyyymmdd
- Power automate date format from excel
- Power automate date format SharePoint
- Power automate format date from UTC
- Power automate format date null
- Power automate get current date time function
- Power automate get current Month name
Introduction to Power automate date functions
The Power Automate Date and Time functions are used to retrieve the date and time, change the time zones, obtain certain information about the date and time, and perform other date/time manipulation.
List of Power Automate date functions
Here we will discuss the List of Power Automate Date Functions.
Function | Description | Syntax |
---|---|---|
addDays | This function is used in Power Automate to add the number of days to an existing timestamp. And also we can use a negative number to deduct days. | addDays(‘<timestamp>’, <days>,'<Format>’?) |
addHour | This function is used in Flow to add the number of Hours to an existing timestamp. Also, we can use a negative number to deduct hours. | addHour ( ‘<timestamp>’, <hour>,'<Format>’?) |
addMinutes | This function is used in Flow to add the number of minutes to an existing timestamp. Also, we can use a negative number to deduct minutes. | addMinutes ( ‘<timestamp>’, <minutes>,'<Format>’?) |
addSeconds | This function is used in Flow to add the number of seconds to an existing timestamp. Also, we can use a negative number to deduct seconds. | addSeconds( ‘<timestamp>’, <seconds>,'<Format>’?) |
addToTime | This function is used in Flow to add the number of time units to an existing timestamp. Also, we can use a negative number to deduct the timestamp. | addToTime( ‘<timestamp>’, <interval>, ‘<timeUnit>’, ‘<Format>’?) |
convertFromUtc | This function in Flow converts an existing timestamp from UTC (Universal Time Coordinated) to the targeted time zone | convertFromUtc (‘<timestamp>’,'<destinationTimeZone>’ ,'<format>?) |
convertTimeZone | This Function in flow converts a timestamp from the source time zone to the target time zone | convertTimeZone (‘<timestamp>’, <sourceTimeZone>, ‘<destinationTimeZone>’, ‘<formats>’?) |
convertToUtc | The function converts an existing timestamp to Universal Time Coordinated(UTC) | convertToUtc (‘<timestamp>’, <sourceTimeZone>, ‘<formats>’?) |
dayOfMonth | This expression in the Flow return day of the month from an existing timestamp | dayOfMonth (‘<timestamp>’) |
dayOfWeek | This expression in the Flow returns the day of the week from an existing timestamp | dayOfWeek (‘<timestamp>’) |
dayOfYear | This expression in the Flow returns the day of the year from an existing timestamp. | dayOfYear (‘<timestamp>’) |
formatDateTime | This expression in the flow returns the date format from the timestamp. | formatDateTime (variables(‘<variable name>’),’yyyy-MM-dd’) |
getFutureTime | This function in the flow returns the current timestamp plus the specified time units | getFutureTime (<interval>, <timeUnit>, <format>?) |
getPastTime | This function in the flow returns the current timestamp minus the specified time units | getPastTime ( <interval>, <timeUnit>, <format>?) |
startOfDay | This function in the flow returns the start of the day for a timestamp | startOfDay (‘<timestamp>’, ‘<format>’?) |
startOfHour | This function in the flow returns the start of the hour for a timestamp | startOfHour (‘<timestamp>’, ‘<format>’?) |
startOfMonth | This function in the flow returns the start of the Month for a timestamp | startOfMonth (‘<timestamp>’, ‘<format>’?) |
substractFromTime | This function in the flow subtracts the number of time units from a timestamp | substractFromTime( ‘<timestamp>’, <interval>, ‘<timeUnit>’, ‘<Format>’?) |
ticks | This function in the flow returns the ticks property value for a specified timestamp | ticks (‘<timestamps>’) |
utcNow | This function in the flow returns the current timestamp as a string | utcNow() |
Read Power Automate Get Data from Excel on SharePoint
Power automate date format dd/mm/yyyy
Here we will see how to format the date ‘dd/MM/yyyy’ using formatDateTime() in Microsoft Flow.
So we will create a flow that will trigger manually to format the current date to dd/MM/yyyy. So for this, we will initialize the variable to store the current date and then we will use the Compose action to format the date.
- Create a manually trigger a flow. Then click on the next step.
- Select the Initialize variable action, provide the ‘name’ of the variable is Date and type string. Next we will set the value, click on the dynamic content-> Expression part and write the below expression:
utcNow()
- Then click on the next step, select the ‘Compose’ action.
- For input click on the dynamic content – expression and write the below expression to format the current date:
formatDateTime(variables('Date'),'dd/MM/yyyy')
- Click on Save and run the flow manually. And now we can see our ouput which returns date format we have formatted.
Read Power Automate Forms to Excel
Power automate date format mm/dd/yyyy
Here we will see how to format the date ‘MM/dd/yyyy’ using formatDateTime() in Microsoft Flow.
So we will create a flow that will trigger manually to format the custom date to ‘MM/dd/yyyy’. So for this, we will initialize the variable to store the date and then we will use the Compose action to format the date.
- Create a Manually Tiggered flow in Power automate. Click on the Next step.
- Then select intialize variable action and provide the variable name as ‘Date’ and type as ‘String’. Then provide the custom value as below:
2021-06-09T13:45:30Z
- Click on the Next step and select the ‘Compose’, then provide the input, click on the dynamic content-> expression, and write the below Expression:
formatDateTime(variables('Date'),'MM/dd/yyyy')
Then click on Save and run the flow manually, now you can see the result in the output in the compose action.
Read Power Automate create an HTML table
Power automate date format yyyymmdd
Here we will see how to convert date to number(yyyyMMdd) using the formatDateTime function in Microsoft Flow.
So we will create a flow that will trigger manually to format the custom date to number (yyyyMMdd). So for this, we will initialize the variable to store the date and then we will use the Compose action to format the date.
- Create a manually triggered flow in Power automate. Then click on the next step.
- Select the initialize variable action, then provide name as Date and Type as string. Then provide the custom value like below:
2021-06-09T13:45:30Z
- Now click on the Next step, choose the ‘Compose’ action and provide the below expression from the dynamic content as input:
formatDateTime(variables('Date'),'yyyyMMdd')
Now save the flow and run the flow manually. We can see the output in the compose action.
Read Power Automate Switch case
Power automate date format from excel
Here we will see how to deal with date column in excel to format the date in Powerautomate using addDays().
So the problem is that when power automate reads the date column from excel, reads as text and stores dates in serial date format. For example, if the date is 2019/01/10 will be read as 43475 within Power Automate.
Therefore by using the below format/ expression we need to convert the number to date within the Power automate.
Syntax:
if(empty(<date to convert>),null,addDays('1899-12-30',int(<date to convert>),'yyyy-MM-dd'))
So first we will create a flow that will trigger manually then it will get all the rows from the below Excel table, and insert the rows into the Html table.
- Create a Manually triggered flow in Power Automate. Then click on Next Step.
- Select ‘List rows present in a table’ action, to get all rows from the excel table. Then provide the location, Document library, File and table of the excel.
- Click on the Next step and select ‘Create Html table’ as action.
- Then in From provide value- List rows present in a table, next click on Advanced option choose custom from the dropdown.
- Then add the Headers and add the value from dynamic content.
- But in date column i.e. Hire Date, we will use the above Format, So write the below exression value:
if(empty(item()?['Hire Date']),null,addDays('1899-12-30',int(item()?['Hire Date']),'yyyy-MM-dd'))
Now save the flow and run the flow manually and you can see the output in the HTML table.
Read Power Automate send an email with an attachment from SharePoint
Power automate date format SharePoint
Here we will see how to format date column from SharePoint list using Power automate.
We will use the below SharePoint list i.e Employee information list and we will use the Hire date column to format the date.
Create a flow using the trigger i.e. When an item is created, so this flow will run when item get created in SharePoint list. Then Provide the site address and list name.
Now click on the next step, then select the Compose action, and in the input write the below expression to format the date column from SharePoint list.
formatDateTime(triggerOutputs()?['body/HireDate'],'yyyy-MM-dd')
Next, save the flow and run the flow, in the compose you can see the output.
Read Power automate approval reminder
Power automate format date from utc
Here we will see how to format the UtcNow()(current date) to ‘yyyy-MM-dd’ using convertTimezone() in Power automate.
So we will create a manually trigger flow, next we will initialize a variable of type string which will store the UTC date, and then using compose action we will format the date.
- Create a manually trigger flow in Power automate. Then click on the next step.
- Select the Initialize variable, then provide the variable name, type as string and value as utcNow().
- Now click on the next step and select the Compose action, and in inputs provide the below expression:
convertTimeZone(variables('Date'),'UTC','Eastern Standard Time','yyyy-MM-dd')
Now save the Flow and run the flow manually. We can see the output result in the compose action.
Read Trigger Conditions in Power Automate
Power automate format date null
Here we will see while formatting the date in power automate, sometimes we get null error, so how to avoid the null error and get the formatted date.
For this, we will use the Employee information SharePoint list to create an HTML table.
So here we will create a flow that will manually trigger, then we will use the Get items action to get all the items from the SharePoint list.
Then we will create an HTML table by using the SharePoint list item. While adding date value we will check the date value is null or not. If the date value is not null then date will format using formatDateTime function.
- Create a manually trigger flow in Power automate. Then click on the next step.
- Select the Get items action, then provide the site address , list name and use Top count as 15.
- Now click on the next step, then select the ‘Create Html table’. Then provide the ‘From’ as value- Get items from the dynamic content.
- Next click on Advanced options, then in columns choose custom from the drop-down. Then add the header name and value. But in Hire date we will add the below expression as value:
if(equals(item()?['HireDate'], null), '', formatDateTime(item()?['HireDate'],'yyyy-MM-dd'))
Now click on save and run the flow manually. Then you can see the result in the output of create an HTML table.
Here you can see where hire date value is not there, it remains null other dates get formatted.
Read Power Automate update SharePoint list item
Power automate get current date time function
Here we will see how to get current date time using date function in Power automate.
To get the current date and time we will use the UtcNow() in the Power automate.
- Create a manually trigger flow in Power automate. Then click on The Next step.
- Select the intialize variable, Give the variable name as Date and type as string and then in the value you can use the below expression, to get current date and time:
formatDateTime(utcNow(),'yyyy-MM-dd HH:mm')
Now click on the save and run the flow manually, and you can see the result in the value:
Read Unable to process template language expressions in action Power Automate
Power automate get current Month name
Here we will see how to get current month name in Power automate.
- Create a manually triggered flow, then click on the next step.
Then select Initialize variable action, give the variable name, type as string and in value add the below expression to get the current month.
formatDateTime(utcNow(),'MMMM')
Now click on the save and run the flow, you can see the output in the value of initialize variable.
You may like the following Power Automate tutorials:
- Move files from OneDrive for Business to SharePoint Online
- Microsoft Flow Example: Copy files from one SharePoint Online account or folder to another
- Microsoft Flow Example Save tweets that include specific hashtag to a SharePoint list
- Microsoft flow examples: Save Office 365 email attachments to OneDrive for Business
- Microsoft Flow or PowerAutomate Example: Send a customized email when a new file is added
- Power Automate String Functions
- Power Automate send email based on form response
- Power Automate list rows present in a table filter query
- Power Automate Compose
- Power Automate Create Document Library
- Power Automate IF
In this Power automate tutorial we learned different types of date functions in Power Automate. And also we discuss the below points:
- Power automate date functions
- Power automate date format dd/mm/yyyy
- Power automate date format mm/dd/yyyy
- Power automate date format yyyymmdd
- Power automate date format from excel
- Power automate date format SharePoint
- Power automate format date from utc
- Power automate format date now
- Power automate format date null
- Power automate get current date time function
- Power automate get current Month name
Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint.com and SPGuides.com
thank you very much for the “get from SPL, check for empty, format if not null” tip.
This issue was driving me crazy in a “add/update Excel from SPL” Flow. Without formating: Flow runs fine. With: all of a sudden it complained that “date field is null”.
Still got the problem that somehow, SPL or aAutomate mix up some of the dates – resulting in MM/dd/yyyy instead of dd/MM/yyyy.
Which is very confusing, since all other dates are sent correct.
Very helpful. Thank you, Bhawana.