Stop Guessing: Power Automate formatDateTime and the Timezone Trap Nobody Warns You About

Your flow emailed the whole team: “Reminder: budget review at 3:00 PM.” The meeting was at 10:00 AM. Your manager forwarded the email with a single question mark, and the flow you were proud of just lost its credibility. It is a common scenario, and the frustrating part is this: Power Automate formatDateTime almost certainly did exactly what you asked.

Most “wrong date” bugs are not formatting bugs at all. They are timezone and language assumptions you never made explicit. formatDateTime is a label maker, not a clock. It formats the text you hand it. It converts nothing.

That distinction matters for a business reason, not a technical one. Automated emails, approvals, and reports only replace the manual process when people trust them, and nothing burns trust faster than a wrong timestamp. Get dates right once and your flow becomes the official record instead of the thing everyone double-checks.

Reading this and testing the examples in your own flow takes about 20 minutes. If expressions themselves still feel new, start with our beginner’s guide to Power Automate expressions and come back. You will leave with one mental model, a trap table, a debugging playbook, and a reference table worth bookmarking.

Convert First, Format Second: The Power Automate Convert Timezone Pattern

SharePoint, Dataverse (Microsoft’s business database, like a super-powered Excel), and many trigger outputs hand your flow dates in UTC. A trigger is the event that starts your flow, an action is what the flow does in response, and UTC is the world’s neutral reference clock, the same everywhere and adjusted for nobody. Microsoft’s workflow expression functions reference is built around this: utcNow() returns the current timestamp in UTC, and that is the shape most connector data arrives in. Connector behavior varies, so check the raw value in run history rather than assuming.

One qualification for Dataverse users: Dataverse date columns come in three behaviors, and the common “User Local” behavior stores UTC under the hood, which is what your flow receives. “Time Zone Independent” and “Date Only” columns behave differently. If you are not sure which you have, assume UTC and verify in run history.

So the pattern is a strict two-step, in this order:

1. Convert the time with convertTimeZone. This changes the actual clock value.
2. Format the text with formatDateTime. This changes only how it looks.

Think of it this way: formatDateTime changes the outfit, convertTimeZone changes the person. If the time itself is wrong, no format string on earth will fix it.

Even better, convertTimeZone accepts a format as its final parameter, so one expression does both jobs:

convertTimeZone(triggerBody()?['StartTime'], 'UTC', 'Eastern Standard Time', 'M/d/yyyy h:mm tt')

Two practitioner notes the docs will not volunteer. First, Power Automate wants Windows timezone names like Eastern Standard Time or W. Europe Standard Time (the full list is on Microsoft Learn), not the America/New_York style you may have seen elsewhere. Second, the word “Standard” in those names is misleading in a harmless way: Eastern Standard Time covers daylight saving too, and the conversion auto-adjusts based on the date. Never hardcode an offset like addHours(x, -5). It works until the clocks change, and then it breaks twice a year, quietly.

The diagnostic rule: if a displayed time is off by a consistent offset (whole hours, or half hours for zones like India Standard Time), it is a timezone problem. Stop editing the format string. Add the conversion.

Power Automate formatDateTime Tokens That Bite

Once the time itself is correct, the format string decides how it reads. These tokens come from .NET’s custom date and time format strings, and one cited sentence is all that connection deserves: the Microsoft Learn reference defines MM as “The month, from 01 through 12” and mm as “The minute, from 00 through 59.”

Read that again, because case sensitivity is the villain of this entire section. MM is month, mm is minutes. HH is 24-hour time, hh is 12-hour time and needs tt for AM/PM. dd is the day number, ddd is “Mon”, dddd is “Monday”. One wrong keypress and December becomes minute 12.

The other bite is literal text. If you want the word “at” inside your output, wrap it in single quotes inside the format string: dddd 'at' h tt. Leave it unquoted and the stray letters get read as tokens, so the t in “at” becomes an AM/PM marker and the output turns to garbage.

One wrinkle the docs bury: the format string itself already sits inside single quotes in the full expression. A single quote inside it must therefore be doubled. The complete, paste-ready form is:

formatDateTime(utcNow(), 'dddd ''at'' h tt')

Here are the traps that come up again and again, in one table. Outputs are shown for the frozen input 2025-12-05T15:47:00Z so you can reproduce every row in a Compose action:

You typed	You expected	You got	Use instead
mm/dd/yyyy	12/05/2025	47/05/2025 (minutes!)	MM/dd/yyyy
yyyy-mm-dd	2025-12-05	2025-47-05	yyyy-MM-dd
hh:mm	15:47	03:47	HH:mm
HH:mm tt	3:47 PM	15:47 PM	hh:mm tt
dd, MMMM	Friday, December	05, December	dddd, MMMM
dddd at h tt	Friday at 3 PM	garbled tokens	dddd ''at'' h tt (doubled quotes in a full expression)
MMMM (no locale)	décembre	December	add 'fr-fr' as the third parameter

Say it as a rule, not a suggestion: month and 24-hour tokens are always uppercase.

The hidden third parameter: locale

formatDateTime takes an optional third argument, a locale, and the Microsoft Learn functions reference documents that it defaults to en-US when you leave it out. That default is consistent, which is both the good news and the trap. Your flow does not mysteriously change language per reader, but it also outputs “Friday” and “December” to your Munich colleagues forever unless you say otherwise.

The docs mention the parameter in passing. Production teaches you that a flow copied for a French or German team keeps its English dates until someone finds this argument. One example:

formatDateTime('2025-12-05', 'dddd d MMMM yyyy', 'fr-fr')

Output: vendredi 5 décembre 2025. Same date, same tokens, different audience.

Takeaway: if a human reads the output, set the locale on purpose. Every time.

Battle-Tested Patterns for SharePoint, Dataverse, and Excel

Here is the rule, and it is the right way, not one option among several: store and move dates in ISO 8601, and make them pretty only at the last step before a human sees them. ISO 8601 is the sortable yyyy-MM-ddTHH:mm:ssZ shape, the format machines agree on. Systems exchange ISO; people get the friendly version in the final email or Teams message.

Power Automate format date SharePoint: write ISO values into SharePoint date columns and let the column’s own display settings handle rendering. Formatting the date into something pretty before writing it is the common mistake, and it quietly turns your date handling into text handling. If you are building on lists already, our SharePoint list automation tutorial shows this write pattern in a full working flow.

One honest caveat: writing a bare yyyy-MM-dd for date-only values reduces the classic off-by-one shift, but it does not guarantee it away. A Date and Time column with local-time behavior can still shift a date near midnight. Write one test item, then check both the list view and the raw connector output in run history before you trust the pattern in your environment.

Power Automate format date Excel is its own small adventure. Excel dates arrive through the connector (a pre-built bridge to another app) either as ISO strings or as serial numbers, depending on the action’s DateTime Format parameter, which the Excel Online (Business) connector reference documents on “List rows present in a table.” Set it to ISO 8601 when you can; the parameter names in the designer shift between connector versions, so look for it under the action’s advanced options.

When you cannot, and a date arrives as a number like 45996, that number is days counted from December 30, 1899. This is a pattern to paste, not memorize:

formatDateTime(addDays('1899-12-30T00:00:00Z', int(item()?['Deadline'])), 'yyyy-MM-dd')

Use the full timestamp form of the base date shown here; bare date strings work in some runtimes and fail in others, so test it once in a Compose before trusting it. The int() drops any fractional part, which is fine for date-only columns. The fraction is the time of day, so if you need the time too, this date-only version is not your pattern.

Finally, null safety. An empty date crashes formatDateTime with a red error in run history, and blank dates happen constantly in real business data. Guard the expression:

if(empty(item()?['DueDate']), 'No due date', formatDateTime(item()?['DueDate'], 'MMM d, yyyy'))

Add that guard and you just made your flow survive missing data. No code, one expression, and one whole class of failed runs gone.

Find the Bad Date in Five Minutes

When a date looks wrong, do not touch the format string yet. Every Power Automate datetime expression problem yields to the same isolation loop, and each pass takes about five minutes:

1. Open run history and click the failed or suspicious action.
2. Read the raw INPUT value, not the output. Is the source value already wrong or empty before your expression ever touched it? Then the expression is innocent.
3. Drop in a Compose action (a scratchpad step whose only job is to show you a value) and paste your expression into it, alone. Run the flow. Now you see exactly what the expression produces without the noise of the surrounding step.
4. Decode the symptom:
   • Off by a consistent offset: timezone. Add convertTimeZone.
   • Weird numbers or characters: format tokens. Check case and literals against the trap table above.
   • Right date, wrong language: locale. Add the third parameter.
   • Red error: null or non-date input. Add the guard pattern.
5. Fix the one thing the symptom points to, rerun, and resist the urge to change two things at once.

Takeaway: never edit the format string until you have looked at the raw input. The expression is often innocent, and step 2 proves it before you waste an hour rewriting it.

What to Tell IT

Good news for your access level: formatDateTime, convertTimeZone, and Compose are expression functions inside your flow, not connectors, so they do not appear in DLP policies (DLP = IT’s rules on which apps can connect) and need no premium license. If you can already build the flow, you can use everything in this article. The connectors around them, like SharePoint or Excel Online, are still governed by your organization’s DLP policy and environment permissions (an environment is a workspace for your apps and flows), so nothing here changes what you are allowed to connect.

Ask IT one thing anyway: your organization’s reference timezone and standard date display convention. Frame it as “I want my flow’s emails to match company standards. What timezone and date format should automated messages use?” That question costs you two minutes, signals you build responsibly, and makes your next request to IT an easier conversation.

The Paste-and-Go Power Automate Date Format Reference

Swap utcNow() for your own dynamic content. Every expression is complete as written; verify each one in a Compose action in your own environment before shipping, because a reference you have tested yourself is the only reference worth trusting.

Desired output	Exact expression
ISO 8601 (storage, SharePoint, APIs): 2025-12-05T14:30:00Z	formatDateTime(utcNow(), 'yyyy-MM-ddTHH:mm:ssZ')
US short date: 12/05/2025	formatDateTime(utcNow(), 'MM/dd/yyyy')
EU short date: 05/12/2025	formatDateTime(utcNow(), 'dd/MM/yyyy')
Long friendly date: Friday, December 5, 2025	formatDateTime(utcNow(), 'dddd, MMMM d, yyyy', 'en-us')
12-hour time: 2:30 PM	formatDateTime(utcNow(), 'h:mm tt')
Local date and time in one step: 12/5/2025 9:30 AM	convertTimeZone(utcNow(), 'UTC', 'Eastern Standard Time', 'M/d/yyyy h:mm tt')
Month name only: December	formatDateTime(utcNow(), 'MMMM', 'en-us')
Localized long date: vendredi 5 décembre 2025	formatDateTime(utcNow(), 'dddd d MMMM yyyy', 'fr-fr')

Bookmark this table; it answers the question faster than a new search will.

Convert first, format second, locale on purpose. With that model in your head, a wrong date stops being a mystery and becomes a five-minute diagnosis, and you now debug by symptom instead of by guesswork. That is a real skill your next flow inherits for free.