The Power Automate formatDateTime function is definitely one of the hardest to understand. At first, these weird formatting strings look like some encrypted messages by aliens. Locales are even worse. People create crazy complex flows, because they do not understand how easy it is to translate days and month into their language with locales.
This guide should be a reference for you! My clear goal is to make you understand, how things work. It is not that hard, as it may look.
Let’s finally understand how to format date and time in Power Automate in every language! Do not forget to bookmark the article :-)!
Power Automate FormatDateTime Function
Formats a given timestamp in a given format and locale.
Syntax
formatDateTime('<timestamp>', '<format>'?, '<locale>'?)Input parameters
- timestamp (mandatory): The timestamp string that needs to be formatted.
- format (optional): A format pattern string that is either a single format specifier or a custom format pattern. (default format = the ISO 8601 compliant “o” format yyyy-MM-ddTHH:mm:ss.fffffffK)
- locale (optional): Locale format to country and language specific format. (default = en-us)
Return Value
- The formatted timestamp according to given format and locale.
Power Automate formatDateTime function examples
Here are some samples to get a feeling for the function. You’ll find plenty of examples in the upcoming sections.
formatDateTime('12/24/2022')
=> 2022-12-24T00:00:00.0000000
formatDateTime(utcNow())
=> 2022-08-13T13:55:44.4848515Z
// With single format pattern
formatDateTime(utcNow(),'U')
=> Saturday, August 13, 2022 2:01:59 PM
// With custom format pattern
formatDateTime('12/24/2022','yyyy-MM-dd')
=> 2022-12-24
// With german locale and custom format pattern
formatDateTime('12/24/2022','dddd','de-de')
=> SamstagHow to format date and time in Power Automate
If you’ve read the description of the formatDateTime function above, you already know :-). There is some obscure format string, which is used to format dates. Let’s bring some light to the dark!
You have two options to format your date and time:
- Use a single format specifier
- Use a custom format pattern
When to use which one? I would recommend using the single format specifier in favor of a custom pattern whenever there is a single format specifier that fits your need.
Why? The single format specifier are in some cases a better fit for localization. A custom format pattern is not flexible, when it comes to different international date formats concerning order of elements.
What is a single format specifier?
It is a one character case-sensitive alias for a date format pattern.
How do you know, which single format specifier to use?
Check out our list of single format specifier to decide, if there is one that fits your need. In case there is no specifier fitting your need, you can define a custom date pattern.
Let’s take a look at a few single format specifier examples!
Single format specifier ‘D’
Take a look at the ‘D’ as the last parameter of formatDateTime!
formatDateTime('12/24/2022 12:27:59','D')
=> Saturday, December 24, 2022Single format specifier ‘T’
formatDateTime('12/24/2022 12:27:59','T')
=> 12:27:59 PMThe T format does not contain any date information.
Single format specifier ‘F’
formatDateTime('12/24/2022 12:27:59','F')
=> Saturday, December 24, 2022 12:27:59 PMThe F format does contain date and time information.
There are more specifiers for almost any use case. Take a look at hour list of single format specifiers to find the best one for your needs!
Although there are a lot of single format specifiers, sometimes you want something different. This is where custom format patterns are the right choice.
What is a custom format pattern?
In contrast to a single format specifier, a custom format pattern contains of multiple characters.
The pattern string is a combination of aliases for a certain format option. There are multiple aliases for every date component like year, month, day, hour, minutes and seconds. See the list of custom format patterns here.
Let’s imagine you want to display a date like this: 2022 December
This is the year with all 4 characters followed by the month as a word.
Let’s take a look at our list of custom format patterns! Is there something that is a good fit?
- yyyy is perfect for the year
- MMMM is what we need for the month
So our custom pattern would look like this: ‘yyyy MMMM’
Let’s give it a try!
formatDateTime('12/24/2022 12:27:59','yyyy MMMM')
=> 2022 DecemberIt works! I hope you get how to formulate your own pattern. Here are some examples of common patterns.
Common Power Automate formatDateTime Pattern
Some common Power Automate formatDateTime formats examples.
MM/dd/yyyy
formatDateTime('12/24/2022 12:27:59','MM/dd/yyyy')
=> 12/24/2022yyyy
formatDateTime('12/24/2022 12:27:59','yyyy')
=> 2022yyyy-MM
formatDateTime('12/24/2022 12:27:59','yyyy-MM')
=> 2022-12yyyy-MM-dd
formatDateTime('12/24/2022 12:27:59','yyyy-MM-dd')
=> 2022-12-24dd-MM-yyyy
formatDateTime('12/24/2022 12:27:59','dd-MM-yyyy')
=> 24-12-2022yyyy-MM-dd
formatDateTime('12/24/2022 12:27:59','yyyy-MM-dd')
=> 2022-12-24dd/MM/yyyy
formatDateTime('12/24/2022 12:27:59','dd/MM/yyyy')
=> 24/12/2022yyyyMMdd
formatDateTime('12/24/2022 12:27:59','yyyyMMdd')
=> 20221224hhmmss
formatDateTime('12/24/2022 12:27:59','hhmmss')
=> 122759Locales
Locales within formatDateTime solve two problems:
- Different date formats in different countries
- Translation on language specific date parts.
You do not know which locale to use for your country? Take a look at the list of locales.
Local differences in date formats
The default locale for formatDateTime is ‘en-US’ for the United States of America.
So here is an example for the US with single format specifier d.
formatDateTime('12/24/2022 12:27:59','d')
=> 12/24/2022Let’s do the same operation for Japan with locale ‘ja-JP’.
formatDateTime('12/24/2022 12:27:59','d','ja-JP')
=> 2022/12/24The order changed from month/day/year to year/month/day.
Let’s try France with locale ‘fr-FR’.
formatDateTime('12/24/2022 12:27:59','d','fr-FR')
=> 24/12/2022Now we have a third variant, with day/month/year.
You see how this can be useful?
Do you need to mail customers in different countries? Do you need to display a date within the mail?
Yes? Use a single format specifier with the customer’s locale, and your mails will be more professional.
Translation of month and week days
This one is more obvious. For instance, January and Monday need to be translated. Once again locales are your friend.
So here is an example for the US with single format specifier f.
formatDateTime('12/24/2022 12:27:59','f')
=> Saturday, December 24, 2022 12:27 PMLet’s to the same operation for Germany’s locale ‘de-DE’.
formatDateTime('12/24/2022 12:27:59','f','de-DE')
=> Samstag, 24. Dezember 2022 12:27There are a couple of changes:
- Day and month have been translated
- PM is removed because germany uses a 24 hour system
- A ‘.’ is used instead of a ‘,’.
This is how you use Power Automate formatDateTime to display local time.
I hope you get the concept. Just for ease of use some more examples for different languages.
Examples – Month translations
Power Automate formatDatetime month name with pattern ‘MMMM’ translated into different languages.
formatDateTime(Date,Format,Locale)
=> Result
// Example
formatDateTime('12/24/2022','MMMM','de-DE')
=> Dezember| Locale | Language | Region | Format | Date | Result |
|---|---|---|---|---|---|
| da-DK | Danish | Denmark | MMMM | 12/24/2022 | december |
| de-DE | German | Germany | MMMM | 12/24/2022 | Dezember |
| en-GB | English | United Kingdom | MMMM | 12/24/2022 | December |
| es-ES | Spanish | Spain | MMMM | 12/24/2022 | diciembre |
| fr-FR | French | France | MMMM | 12/24/2022 | décembre |
| nl-NL | Dutch | The Netherlands | MMMM | 12/24/2022 | december |
| pt-BR | Portugese | Brazil | MMMM | 12/24/2022 | dezembro |
Examples – Day translations
formatDateTime(Date,Format,Locale)
=> Result
// Example
formatDateTime('12/24/2022','dddd','de-DE')
=> Samstag| Locale | Language | Region | Format | Date | Result |
|---|---|---|---|---|---|
| da-DK | Danish | Denmark | dddd | 12/24/2022 | lørdag |
| de-DE | German | Germany | dddd | 12/24/2022 | Samstag |
| en-GB | English | United Kingdom | dddd | 12/24/2022 | Saturday |
| es-ES | Spanish | Spain | dddd | 12/24/2022 | sábado |
| fr-FR | French | France | dddd | 12/24/2022 | samedi |
| nl-NL | Dutch | The Netherlands | dddd | 12/24/2022 | zaterdag |
| pt-BR | Portugese | Brazil | dddd | 12/24/2022 | sábado |
Timezones
I know it is a little off-topic, but besides locales, there is another local component when it comes to date and time – timezones.
You may get timestamps from a source that is not in your timezone or you need today’s date by using utcNow. You need to transfer the timestamp into your timezone.
You can use the convertTimeZone function for this. Like this:
convertFromUtc(utcNow(), 'Central Standard Time (Mexico)')Looking for your timezone? Take a look at the list of timezones to find the timezone you need.
Example – Power Automate formatDateTime with Timezone
This example converts the timezone to Germans time and uses the Germans locale.
formatDateTime(convertFromUtc(utcNow(),'W. Europe Standard Time'),'F','de-DE')
=> Sonntag, 14. August 2022 12:29:41More Power Automate formatDateTime Examples
Let’s dive deeper into Power Automate date formats
Date without time / Date only / remove time
formatDateTime('12/24/2022 12:27:59','d')
=> 12/24/2022Today’s date
formatDateTime(utcNow(),'d')
=> 12/24/2022Time only
formatDateTime(utcNow(),'d')
=> 12/24/2022Yesterday
formatDateTime(subtractFromTime(utcNow(),1,'Day'),'D')
=> Saturday, August 13, 2022Month number
formatDateTime('12/24/2022 12:27:59','MM')
=> 121 hour ago
formatDateTime(subtractFromTime(utcNow(),1,'Hour'),'F')
=> Sunday, August 14, 2022 9:39:34 AM30 days ago
formatDateTime(subtractFromTime(utcNow(),30,'Day'),'F')
=> Friday, July 15, 2022 10:40:22 AM30 minutes ago
formatDateTime(subtractFromTime(utcNow(),30,'Minute'),'F')
=> Sunday, August 14, 2022 10:11:01 AMReference A – Format Options: Single format specifier
A single format specifier is a date formatting pattern expressed by a single character.
To understand which character stands for which pattern, we have created a sample table.
In case the table is not self explaining, this pseudocode and a concrete example might help to understand the table.
// How to read the table
formatDateTime(Date,Format)
=> Formatted Date (en-US)
// Example for first line
formatDateTime('12/24/2022 12:27:59','d')
=> 12/24/2022Note: All examples use locale ‘en-US’. Different locales may cause a different order of elements. For example, for specifier ‘d’: ja-JP = ‘2022/12/24′ , fr-FR = ’24/12/2022’.
| Format | Date | Formatted Date (en-US) |
|---|---|---|
| d | 12/24/2022 12:27:59 | 12/24/2022 |
| D | 12/24/2022 12:27:59 | Saturday, December 24, 2022 |
| f | 12/24/2022 12:27:59 | Saturday, December 24, 2022 12:27 PM |
| F | 12/24/2022 12:27:59 | Saturday, December 24, 2022 12:27:59 PM |
| G | 12/24/2022 12:27:59 | 12/24/2022 12:27:59 PM |
| M or m | 12/24/2022 12:27:59 | December 24 |
| O or o | 12/24/2022 12:27:59 | 2022-12-24T12:27:59.0000000 |
| R or r | 12/24/2022 12:27:59 | Sat, 24 Dec 2022 12:27:59 GMT |
| s | 12/24/2022 12:27:59 | 2022-12-24T12:27:59 |
| t | 12/24/2022 12:27:59 | 12:27 PM |
| T | 12/24/2022 12:27:59 | 12:27:59 PM |
| u | 12/24/2022 12:27:59 | 2022-12-24 12:27:59Z |
| U | 12/24/2022 12:27:59 | Saturday, December 24, 2022 12:27:59 PM |
| Y or y | 12/24/2022 12:27:59 | December 2022 |
Reference B – Format Options: Custom date and time format strings
You find the formats explained for every date and time component below. Combine these formats to build the format, you need!
Year
| Format | Date | Formatted Date |
|---|---|---|
| y | 0001-03-01 | 1 |
| y | 2000-11-15 | 0 |
| y | 2022-03-01 | 22 |
| yy | 0001-03-01 | 01 |
| yy | 2000-11-15 | 00 |
| yy | 2022-03-01 | 22 |
| yyy | 0001-03-01 | 001 |
| yyy | 0090-03-01 | 090 |
| yyy | 2022-03-01 | 2022 |
| yyyy | 0001-03-01 | 0001 |
| yyyy | 2022-03-01 | 2022 |
| yyyyy | 2022-03-01 | 02022 |
Month
| Format | Date | Formatted Date |
|---|---|---|
| M | 2022-03-01 | 3 |
| M | 2022-11-15 | 11 |
| MM | 2022-03-01 | 03 |
| MM | 2022-11-15 | 11 |
| MMM | 2022-03-15 | Mar |
| MMMM | 2022-03-15 | March |
Days
| Format | Date | Formatted Date |
|---|---|---|
| d | 2022-03-01 | 1 |
| d | 2022-03-15 | 15 |
| dd | 2022-03-01 | 01 |
| dd | 2022-03-15 | 15 |
| ddd | 2022-03-15 | Tue |
| dddd | 2022-03-15 | Tuesday |
Hours
| Format | Time | Formatted Time |
|---|---|---|
| h | 01:30 | 1 |
| h | 11:30 | 11 |
| hh | 01:30 | 01 |
| hh | 11:30 | 11 |
| hh | 13:30 | 01 |
| H | 01:30 | 1 |
| H | 13:30 | 13 |
| HH | 01:30 | 01 |
| HH | 13:30 | 13 |
| t | 01:30 | A |
| t | 13:30 | P |
| tt | 01:30 | AM |
| tt | 13:30 | PM |
Minutes
| Format | Time | Formatted Time |
|---|---|---|
| m | 01:02 | 2 |
| m | 11:30 | 30 |
| mm | 01:02 | 02 |
| mm | 11:30 | 30 |
Seconds
| Format | Time | Formatted Time |
|---|---|---|
| s | 01:00:02 | 2 |
| s | 11:00:30 | 30 |
| ss | 01:00:02 | 02 |
| ss | 11:00:30 | 30 |
