Ever set up a meeting on the wrong date because the calendar was in a different format? Or been baffled by a delivery date on an online order due to differences in day-month and month-day formats?
If you’re working with Power Automate, correctly formatting dates and times is essential to avoiding unwanted time-travel incidents.
FormatDateTime is a function in Power Automate that helps you customize how you display date and time values. It’s a tool that enables you to define a particular format for displaying date and time values, irrespective of the user’s locale or system settings. It’s like having a universal translator but for date and time formats that ensure everyone’s on the same page (or at the same point in time!).
This article provides a comprehensive guide on how to use the FormatDateTime function effectively in Power Automate. From understanding the basics of the function and how to use it efficiently, to advanced techniques and best practices, we’ll be covering it all.
So let’s dive right in, because, as we all know, time waits for no one!
What is FormateDateTime in Power Automate?
FormatDateTime is a function available in Power Automate, which is part of Microsoft’s Power Platform, which allows you to convert a DateTime value into a string value based on the format you provide.
In other words, it gives you the flexibility to present the date and time values in a format that is most suitable for your specific use case or audience.
It ensures consistency in how your workflows handle and display date and time values, and it plays a crucial role in maintaining data accuracy and integrity.
The syntax for the formatDateTime() function is:
FormatDateTime(Timestamp, Format)It takes two main arguments:
- Timestamp: The date and time value that needs to be formatted. It can be entered manually (e.g., ‘2023-07-19T14:56:02Z’) or automatically using a function (e.g. utcNow(), which returns the current date and time in Coordinated Universal Time (UTC)).
- Format: A format string that refers to the desired output format. It can consist of a variety of different codes that represent different parts of the date or time (e.g., ‘yyyy’ for 4-digit year, ‘MM’ for 2-digit month, etc.).
When entered manually, the timestamp is expected to follow the ISO 8601 format (yyyy-MM-ddTHH:mm), while the output date format should conform to .NET format specifications.
By providing the timestamp and format string, you can create custom date and time formatting according to their specific needs.
An example of using the formatDateTime function is as follows:
formatDateTime('2023-07-19T00:00:00', 'dd-MM-yyyy')
In this case, the formatDateTime function receives an input date (2023-06-16) with the desired format (dd-MM-yyyy), resulting in the output:
19-07-2023Some common format strings for the formatDateTime function include:
- yyyy: four-digit year, e.g., “2023”
- MM: two-digit month, e.g., “06”
- dd: two-digit day, e.g., “16”
- HH: two-digit hour, e.g., “00”
- mm: two-digit minute, e.g., “00”
- ss: two-digit second, e.g., “00”
By understanding the basics of the formatDateTime function, Power Automate users can fully exploit the capabilities of this function to enhance their workflows and meet specific formatting demands for date and time values.
How to Use FormatDateTime in Power Automate
Alright, now that we’ve gone over the basics of the FormatDateTime function, let’s get our hands dirty and actually use it to generate the time and date in different date formats.
In this section, we’ll walk you through how to use FormatDateTime for efficient date and time formatting, explore common use case scenarios, and troubleshoot common issues.
Step-by-Step Guide to Using FormatDateTime
Here’s a simple step-by-step guide to using the FormatDateTime function in Power Automate.
For our demonstration, we’ll use the Send an email (V2) action to send an email containing the FormatDateTime function. We’ll also use the utcNow() function as the timestamp to automatically fetch the current date and time in Coordinated Universal Time (UTC).
1. Go to make.powerautomate.com, open your Power Automate dashboard and create a new manually triggered cloud flow
2. We chose to send an email when the manual trigger activates and added the following information:
3. In the email body of the workflow, click on ‘Add dynamic content’ and in the ‘Expression’ tab, type ‘formatDateTime()’, ensuring the correct capitalization.
4. Inside the parentheses, add your DateTime value (utcNow() in this case) and then specify your format within quotation marks. For example, to display the date in a ‘Day-Month-Year’ format, you would type ‘dd-MM-yyyy’. Your final function should look something like this:
5. Click ‘OK’ to add this function to your step or action. Power Automate will now use this function to format any DateTime values as specified.
6. After testing the flow, here is the output:
As you can see in the above example, the formatdatetime function has printed out the date in a a ‘Day-Month-Year’ format.
But that’s not the only format you can use! Below is a table showing different formats available and their output:
Please note that the above examples are just a sample of what is possible in Power Automate. The format of the date and time can be customized further according to your needs using these and other codes, as you’ll see in the next section!
6 Advanced formatDateTime Techniques
In this section, we’ll venture beyond the basics and explore advanced techniques with the formatDateTime function in Power Automate.
Understanding these techniques can significantly enhance your data manipulation capabilities, especially when dealing with complex date and time formatting scenarios.
1. Nesting Functions
One of the more advanced uses of formatDateTime involves nesting it within other expressions to achieve more complex outcomes.
Suppose you want to create a reminder for an event happening seven days from now. You could nest addDays function within formatDateTime to achieve this:
Expression:
formatDateTime(addDays(utcNow(), 7), 'yyyy-MM-dd') Output (assuming today is 2023-07-19):
2. Conditional Formatting
formatDateTime can also be used in conjunction with conditional logic to dynamically adjust the formatting of your dates and times based on certain conditions.
For example, you could use an if-condition to format your date in a long-form style if it falls on a weekend, and a short-form style if it’s a weekday.
Expression:
or(equals(dayOfWeek(utcNow()), 0), equals(dayOfWeek(utcNow()), 6))
Assuming it’s a weekday and today’s date is Wednesday 19, 2023, the expression will return ‘False’ and the output will be a short-form style:
However, if it had been the weekend, then the output will be as follows:
3. Handling Time Zones
While formatDateTime handles times in the UTC time zone by default, you can also use it in conjunction with the convertTimeZone function to handle date and time data in other time zones.
If you need to display the current time in the Pacific Standard Time zone, you could use convertTimeZone nested within formatDateTime.
Expression:
formatDateTime(convertTimeZone(utcNow(), 'UTC', 'Pacific Standard Time'), 'yyyy-MM-dd HH:mm')Output assuming current UTC is 19:21:
4. Utilizing Offsets
In addition to handling different time zones, you can also use formatDateTime with time offsets to adjust times based on various factors.
Suppose you need to display a time that’s stored as UTC but should be displayed as UTC+3 to match your local time, you could use addHours with 3 as the last parameter:
Expression:
formatDateTime(addHours(utcNow(), 3), 'yyyy-MM-dd HH:mm') Example assuming current UTC is 19:37:
5. Using Custom Format Specifiers
Using custom format specifiers in Power Automate’s formatDateTime() function allows more flexibility in formatting dates and times. If you use “initialize variable,” you can create a variable containing your custom format specifier for use.
Here are some common custom date format specifiers:
- yyyy: Represents the full year (e.g., 2023)
- yy: Represents the last two digits of the year (e.g., 23)
- y: Represents the year without leading zeros (e.g., 2023)
- MM: Represents the month with a leading zero (e.g., 06 for June)
- dd: Represents the day with a leading zero (e.g., 01 for the first day of the month)
Additionally, there are custom time format specifiers for time:
- HH: Represents the hour using a 24-hour format (e.g., 17 for 5 PM)
- hh: Represents the hour using a 12-hour format (e.g., 05 for 5 PM)
- mm: Represents the minutes with a leading zero (e.g., 07)
- ss: Represents the seconds with a leading zero (e.g., 04)
- ff: Represents the fractions of a second with a single digit value or single character (e.g., 1, 2)
- fff: Represents the fractions of a second with three digits (e.g., 001, 002)
- fffff: Represents the fractions of a second with a five digit number (e.g., 00001, 00002)
- fffffff: Represents the fractions of a second with seven digits (e.g., 0000001, 0000002)
For example, if you want to display the date and time as “July 19, 2023, 19:55,” you can use the following formatDateTime() function with a custom date and time format specifier:
formatDateTime(utcNow(), 'MMMM dd, yyyy, HH:mm')
Output:
6. Combining Format Specifiers
You can combine multiple format specifiers to achieve more complex date and time formatting, such as including the am/pm indicator or using different separators.
To include the am/pm indicator, you can use the ‘tt’ format specifier. Here’s an example:
formatDateTime(utcNow(), 'MM/dd/yyyy hh:mm tt')
The above function would give you a formatted date and time like:
Moreover, you can use different separators for the date and time components. For instance, if you’d like to use dashes for the date and colons for the time, you can modify the format specifier accordingly:
formatDateTime(utcNow(), 'MM-dd-yyyy HH:mm:ss')
This will return a formatted date and time as:
The formatDateTime function in Power Automate offers a comprehensive set of features that allows you to manipulate and format date and time data flexibly.
From nesting functions to conditional formatting, handling different time zones, and utilizing offsets, the advanced techniques we’ve discussed expand your toolbox and provide you with the skills to handle intricate date and time-related tasks.
By mastering these techniques, you’ll be equipped to optimize your Power Automate Flows, providing dynamic, accurate, and relevant outputs, thereby enhancing the overall efficiency and effectiveness of your automation workflows.
Troubleshooting Common Issues With FormatDateTime
As with any function, you may run into issues while using FormatDateTime. Here are some common problems and solutions:
1. Incorrect Format Specifier: Ensure you’re using the correct letters in your format string. For example, ‘MM’ represents the month, while ‘mm’ represents minutes.
2. Invalid DateTime Value: If the DateTime value you’re trying to format is invalid or null, the function will return an error. Always ensure that your DateTime values are valid and non-null.
3. TimeZone Considerations: FormatDateTime does not handle time zone conversions. If you need to convert between time zones, consider using the ConvertTimeZone function before formatting the DateTime.
Remember, mastering FormatDateTime is a matter of practice and understanding the needs of your workflow. With these tips in your toolbox, you’re well on your way to becoming a FormatDateTime expert.
Final Thoughts
Just like mastering the art of time management is crucial to accomplishing tasks efficiently in life, mastering the art of date and time formatting in Power Automate is key to ensuring your workflows run seamlessly.
As we’ve seen, the FormatDateTime function plays a vital role in this aspect. It’s your trusty companion that helps you navigate the temporal aspects of your workflows, ensuring consistency, accuracy, and user-friendliness.
Whether you’re formatting date and time values for international collaborators, tailoring them for customer interactions, or ensuring data consistency for integrations, FormatDateTime is an indispensable tool in your Power Automate arsenal!
To learn more about how to use Power Automate, check out this video on how to integrate ChatGPT with Outlook using Power Automate:
Frequently Asked Questions
How do I use FormatDateTime in Power Automate?
To use FormatDateTime in Power Automate, navigate to the workflow and select ‘Add dynamic content’ in the relevant step. Go to the ‘Expression’ tab and type formatDateTime(). Within the parentheses, add your DateTime value (e.g. utcNow()), a comma, and then the desired format in quotes.
For instance, formatDateTime(utcNow(), ‘MM-dd-yyyy’) would format your DateTime value as month-day-year. Click ‘OK’ to finalize.
How do I format dynamic content in Microsoft Power Automate?
To format dynamic content in Power Automate, add an action or condition in your workflow and select ‘Add dynamic content’. In the expression tab, use the necessary function (like FormatDateTime) and reference your dynamic content.
For example, use formatDateTime(items(‘Apply_to_each’)?[‘YourDateField’], ‘MM-dd-yyyy’) to format a dynamic date, replacing ‘YourDateField’ with your date field’s name. Click ‘OK’ to implement.
How do you format the date in Power Automate Dataverse?
In Power Automate, to format a date from Dataverse, use the FormatDateTime function. During your flow, in the necessary action, select ‘Add dynamic content’ and use the expression formatDateTime(items(‘YourAction’)?[‘YourDataverseDateField’], ‘MM-dd-yyyy’).
Replace ‘YourAction’ with the action that fetches data from Dataverse, ‘YourDataverseDateField’ with your specific date field’s name, and ‘MM-dd-yyyy’ with your preferred date format. Click ‘OK’ to implement the change.
What is the date format for ISO 8601 in Power Automate?
In Power Automate, the ISO 8601 date format can be represented using the FormatDateTime function. The syntax would be formatDateTime(yourDateTimeValue, ‘yyyy-MM-ddTHH:mm:ssZ’).
Replace ‘yourDateTimeValue’ with the DateTime value you’re working with (e.g., utcNow()). This format string ensures the DateTime value follows the ISO 8601 standard, signifying Year-Month-DayTHour:Minute:SecondZ.
