The Power Automate FormatDateTime Function Guide

Power Automate formatDateTime Function 1

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

Power Automate formatDateTime

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')
=> Samstag

How 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:

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, 2022

Single format specifier ‘T’

formatDateTime('12/24/2022 12:27:59','T')
=> 12:27:59 PM

The 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 PM

The 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 December

It 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/2022

yyyy

formatDateTime('12/24/2022 12:27:59','yyyy')
=> 2022

yyyy-MM

formatDateTime('12/24/2022 12:27:59','yyyy-MM')
=> 2022-12

yyyy-MM-dd

formatDateTime('12/24/2022 12:27:59','yyyy-MM-dd')
=> 2022-12-24

dd-MM-yyyy

formatDateTime('12/24/2022 12:27:59','dd-MM-yyyy')
=> 24-12-2022

yyyy-MM-dd

formatDateTime('12/24/2022 12:27:59','yyyy-MM-dd')
=> 2022-12-24

dd/MM/yyyy

formatDateTime('12/24/2022 12:27:59','dd/MM/yyyy')
=> 24/12/2022

yyyyMMdd

formatDateTime('12/24/2022 12:27:59','yyyyMMdd')
=> 20221224

hhmmss

formatDateTime('12/24/2022 12:27:59','hhmmss')
=> 122759

Locales

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/2022

Let’s do the same operation for Japan with locale ‘ja-JP’.

formatDateTime('12/24/2022 12:27:59','d','ja-JP')
=> 2022/12/24

The 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/2022

Now 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 PM

Let’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:27

There 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
LocaleLanguageRegionFormatDateResult
da-DKDanishDenmarkMMMM12/24/2022december
de-DEGermanGermanyMMMM12/24/2022Dezember
en-GBEnglishUnited KingdomMMMM12/24/2022December
es-ESSpanishSpainMMMM12/24/2022diciembre
fr-FRFrenchFranceMMMM12/24/2022décembre
nl-NLDutchThe NetherlandsMMMM12/24/2022december
pt-BRPortugeseBrazilMMMM12/24/2022dezembro

Examples – Day translations

formatDateTime(Date,Format,Locale)
=> Result

// Example

formatDateTime('12/24/2022','dddd','de-DE')
=> Samstag
LocaleLanguageRegionFormatDateResult
da-DKDanishDenmarkdddd12/24/2022lørdag
de-DEGermanGermanydddd12/24/2022Samstag
en-GBEnglishUnited Kingdomdddd12/24/2022Saturday
es-ESSpanishSpaindddd12/24/2022sábado
fr-FRFrenchFrancedddd12/24/2022samedi
nl-NLDutchThe Netherlandsdddd12/24/2022zaterdag
pt-BRPortugeseBrazildddd12/24/2022sá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:41

More Power Automate formatDateTime Examples

Date without time / Date only / remove time

formatDateTime('12/24/2022 12:27:59','d')
=> 12/24/2022

Today’s date

formatDateTime(utcNow(),'d')
=> 12/24/2022

Time only

formatDateTime(utcNow(),'d')
=> 12/24/2022

Yesterday

formatDateTime(subtractFromTime(utcNow(),1,'Day'),'D')
=> Saturday, August 13, 2022

Month number

formatDateTime('12/24/2022 12:27:59','MM')
=> 12

1 hour ago

formatDateTime(subtractFromTime(utcNow(),1,'Hour'),'F')
=> Sunday, August 14, 2022 9:39:34 AM

30 days ago

formatDateTime(subtractFromTime(utcNow(),30,'Day'),'F')
=> Friday, July 15, 2022 10:40:22 AM

30 minutes ago

formatDateTime(subtractFromTime(utcNow(),30,'Minute'),'F')
=> Sunday, August 14, 2022 10:11:01 AM

Reference 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/2022

Note: 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’.

FormatDateFormatted Date (en-US)
d12/24/2022 12:27:5912/24/2022
D12/24/2022 12:27:59Saturday, December 24, 2022
f12/24/2022 12:27:59Saturday, December 24, 2022 12:27 PM
F12/24/2022 12:27:59Saturday, December 24, 2022 12:27:59 PM
G12/24/2022 12:27:5912/24/2022 12:27:59 PM
M or m12/24/2022 12:27:59December 24
O or o12/24/2022 12:27:592022-12-24T12:27:59.0000000
R or r12/24/2022 12:27:59Sat, 24 Dec 2022 12:27:59 GMT
s12/24/2022 12:27:592022-12-24T12:27:59
t12/24/2022 12:27:5912:27 PM
T12/24/2022 12:27:5912:27:59 PM
u12/24/2022 12:27:592022-12-24 12:27:59Z
U12/24/2022 12:27:59Saturday, December 24, 2022 12:27:59 PM
Y or y12/24/2022 12:27:59December 2022
Single format specifier

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

FormatDateFormatted Date
y0001-03-011
y2000-11-150
y2022-03-0122
yy0001-03-0101
yy2000-11-1500
yy2022-03-0122
yyy0001-03-01001
yyy0090-03-01090
yyy2022-03-012022
yyyy0001-03-010001
yyyy2022-03-012022
yyyyy2022-03-0102022

Month

FormatDateFormatted Date
M2022-03-013
M2022-11-1511
MM2022-03-0103
MM2022-11-1511
MMM2022-03-15Mar
MMMM2022-03-15March

Days

FormatDateFormatted Date
d2022-03-011
d2022-03-1515
dd2022-03-0101
dd2022-03-1515
ddd2022-03-15Tue
dddd2022-03-15Tuesday

Hours

FormatTimeFormatted Time
h01:301
h11:3011
hh01:3001
hh11:3011
hh13:3001
H01:301
H13:3013
HH01:3001
HH13:3013
t01:30A
t13:30P
tt01:30AM
tt13:30PM

Minutes

FormatTimeFormatted Time
m01:022
m11:3030
mm01:0202
mm11:3030

Seconds

FormatTimeFormatted Time
s01:00:022
s11:00:3030
ss01:00:0202
ss11:00:3030

Leave a Comment

Your email address will not be published.