Short answer
You format a date in Power Automate with formatDateTime(), for example formatDateTime(utcNow(), 'MM/dd/yyyy') for the current UTC date in US format or 'yyyy-MM-dd' for an ISO date. To display a local date, convert from the timestamp's source time zone first with convertTimeZone(). In the designer, you enter expressions with Insert expression (fx). Expressions always use English function names, commas between arguments, and text in single quotes.
Enter regular expressions with fx in the Inputs of a Compose action or in the field where you need the result. The referenced steps and variables must exist in your flow. Use the Excel expression with items() inside the loop with the internal name Apply_to_each. For the Filter array example, set From to an array whose items each have a Status object with a Value field.
Basic rules
- In the designer, insert expressions with Insert expression (fx) and dynamic content with Insert dynamic content (lightning icon).
- In the fx editor, enter functions such as
equals(...)without a leading@. Start trigger conditions and expressions in the advanced mode of "Filter array" with@. Use Insert expression (fx) in the target field, such as the Inputs of a Compose action. Code View shows how the flow embeds an expression in text with@{...}. Code View is read-only. - Text goes in single quotes:
'Open'. An apostrophe within the text is doubled:'Customer''s order'. - Use
?to access fields that may be missing or null:triggerBody()?['Status']then returnsnullinstead of an error. It doesn't replace a type check. - Use the exact internal action name, with spaces replaced by underscores:
outputs('Get_open_orders'). - Case sensitivity:
contains()andreplace()are case-sensitive,startsWith(),endsWith(), andindexOf()aren't.
Date and time
utcNow() returns the current time in UTC. Timestamps from connectors may be in a different time zone, so check where a value comes from. The examples use a fixed timestamp so you can follow the result. In your flow, use utcNow() instead, for example.
2026-11-16formatDateTime('2026-11-16T12:00:00Z', 'yyyy-MM-dd')
16.11.2026formatDateTime('2026-11-16T12:00:00Z', 'dd.MM.yyyy')
weekday and month in GermanformatDateTime('2026-11-16T12:00:00Z', 'dddd, d. MMMM yyyy', 'de-DE')
16.11.2026 13:00convertTimeZone('2026-11-16T12:00:00Z', 'UTC', 'W. Europe Standard Time', 'dd.MM.yyyy HH:mm')
in 7 daysaddDays(utcNow(), 7, 'yyyy-MM-dd')
30 days agoaddDays(utcNow(), -30, 'yyyy-MM-dd')
start of the current UTC daystartOfDay(utcNow())
0 = Sunday ... 6 = SaturdaydayOfWeek(utcNow())
The convertTimeZone() example uses W. Europe Standard Time for Germany, which accounts for daylight saving time. Choose the zone that matches your location.
Important format characters: dd day, MM month (capital M), yyyy year, HH hour (24-hour), mm minute (lowercase m), ss second. MM and mm are often mixed up.
Comparing dates
Compare ISO date strings only after putting both dates in the same time zone. The ticks example counts whole 24-hour intervals, not calendar days.
trueless(formatDateTime('2026-11-15T12:00:00Z', 'yyyy-MM-dd'), formatDateTime('2026-11-16T12:00:00Z', 'yyyy-MM-dd'))
1div(sub(ticks('2026-11-16T00:00:00Z'), ticks('2026-11-15T00:00:00Z')), 864000000000)
For the calendar day in Germany, convert both values to 'W. Europe Standard Time' with convertTimeZone() first.
Dates from Excel
Check which format the "List rows present in a table" action actually returns. If your action offers ISO 8601 under DateTime Format, select it to retrieve ISO-formatted timestamp strings.
If you get a whole serial number of 61 or higher from the Excel 1900 date system instead, use the conversion below. Other date systems and time portions need their own conversion.
2026-11-16addDays('1899-12-30T00:00:00Z', int('46342'), 'yyyy-MM-dd')
serial number from the current rowaddDays('1899-12-30T00:00:00Z', int(string(items('Apply_to_each')?['OrderDate'])), 'yyyy-MM-dd')
Text
Title, Email, and Customer are text fields in these examples.
concat('Order ', triggerBody()?['Title'], ' approved')
replaces semicolons, case-sensitivereplace(triggerBody()?['Title'], ';', ',')toLower(triggerBody()?['Email'])
also works if the field is emptytrim(coalesce(triggerBody()?['Customer'], ''))
["a","b","c"]split('a;b;c', ';')
"a, b, c"join(createArray('a', 'b', 'c'), ', ')
"2026"substring('2026-11-16', 0, 4)
6, -1 if not foundindexOf('hello world', 'world')
4length('abcd')
text "1.234,50"formatNumber(1234.5, '#,##0.00', 'de-DE')
substring() returns an error if start and length go beyond the end of the text. With variable input, check the text and its length first.
Conditions
Status and Comment are text fields, Amount is a number or null.
if(equals(triggerBody()?['Status'], 'Open'), 'open', 'closed')and(equals(triggerBody()?['Status'], 'Open'), greater(coalesce(triggerBody()?['Amount'], 0), 1000))or(equals(triggerBody()?['Priority'], 'High'), equals(triggerBody()?['Priority'], 'Critical'))
comment is not emptynot(empty(coalesce(triggerBody()?['Comment'], '')))
first value that is not nullcoalesce(triggerBody()?['Phone'], triggerBody()?['Mobile'], 'no number')
coalesce() returns the first value that isn't null. An empty string is kept. For text fields, use empty(coalesce(triggerBody()?['Comment'], '')) to cover both missing and empty values.
JSON and arrays
text to objectjson('{"name": "Contoso", "orders": 3}')
text to arrayjson('["Open","Closed"]')
Compose returned a JSON stringjson(outputs('Compose_response'))?['name']
Compose returned an objectoutputs('Compose_response')?['name']first(body('Get_items')?['value'])last(body('Get_items')?['value'])length(body('Get_items')?['value'])
unique values from both arraysunion(variables('arrA'), variables('arrB'))createArray('Open', 'In progress')
current item of the loop named Apply_to_eachitems('Apply_to_each')?['Title']
current item inside Select or Filter arrayitem()?['Title']
With "Get items", check that value contains items before you use first() or last().
The "Parse JSON" action splits JSON into fields based on a schema, which then appear as dynamic content. You generate the schema from a sample payload. Use "Select" to reshape an array and "Filter array" to keep matching items. Both actions process the array without a loop you build yourself.
Filter array, advanced mode@equals(item()?['Status']?['Value'], 'Open')
Variables and outputs of other steps
variables('varCounter')outputs('Compose_cutoff_date')body('Get_item')?['Title']triggerBody()?['Title']
e.g. "Succeeded"actions('Get_item')?['status']
You create variables with "Initialize variable" at the top level of the flow. Use "Compose" for a value that stays fixed. Only change shared variables in a loop if the loop runs sequentially.
Filter query in SharePoint "Get items"
"Get items" filters on the SharePoint server. Its Filter Query field accepts OData syntax, not the expression language. The examples are individual entries for the Filter Query field. DueDate and Start_x0020_Date are internal names of date and time columns. Country is a single-value lookup, and Title is the internal name of the target column in the referenced list.
| Operator or function | Meaning | Example |
|---|---|---|
eq, ne | equal, not equal | Status eq 'Open' |
gt, ge, lt, le | greater, greater or equal, less, less or equal | Amount gt 1000 |
and, or | combine | Status eq 'Open' and Department eq 'Sales' |
startswith() | starts with | startswith(Title, 'A') |
substringof() | contains | substringof('Contoso', Title) |
single-value lookup, field of the referenced listCountry/Title eq 'Germany'
internal name with an encoded spaceStart_x0020_Date ge '2026-01-01T00:00:00Z'
For a filter based on the current date, use Insert expression (fx) in the Filter Query field and enter a single expression. It produces the OData filter text, for a run on November 16, 2026, for example DueDate lt '2026-11-16T00:00:00Z' (before the start of the current UTC day):
concat('DueDate lt ''', startOfDay(utcNow(), 'yyyy-MM-ddTHH:mm:ssZ'), '''')
Important:
- Use the actual internal column name, not the display name. You'll find it in the address when you open the column in the list settings. Also check the type and the filter value.
- The SharePoint guide from Microsoft documents 100 items by default. Set Top Count and Pagination to match the number of records you need.
- For lists with more than 5,000 items, Microsoft says turning on Pagination in the action settings can help when the first 5,000 items contain no match.
- With Limit Columns by View, you only load the columns of a view.
A server-side filter query can reduce the amount of data transferred considerably compared to "Filter array" after the retrieval.
Trigger conditions
Trigger conditions check in the trigger itself whether the flow should start. If the condition isn't met, the flow doesn't start and no entry appears in the run history.
How to add them in the new designer: select the trigger, open Settings, and select + Add next to Trigger conditions. Paste the expression with @.
Run the flow once before adding the conditions. Compare the SharePoint trigger body with the sample. The sample assumes a single-select Status choice column, a numeric Amount column, and a single-value Editor person field. The examples use Status.Value as text, Amount as a number, and Editor.Email as text. A shortened trigger body looks like this:
{
"Status": {"Value": "Approved"},
"Amount": 1250,
"Editor": {"Email": "editor@example.com"}
}
Each line below is a separate example. Add the condition you need:
@equals(triggerBody()?['Status']?['Value'], 'Approved')@greater(coalesce(triggerBody()?['Amount'], 0), 1000)@or(equals(triggerBody()?['Status']?['Value'], 'Approved'), equals(triggerBody()?['Status']?['Value'], 'Rejected'))@and(not(empty(coalesce(triggerBody()?['Editor']?['Email'], ''))), not(equals(toLower(coalesce(triggerBody()?['Editor']?['Email'], '')), 'serviceaccount@example.com')))
If you add multiple conditions, Power Automate combines them with AND. For "or", use @or(...) in a single condition. The last expression ignores changes made by the specified service account and doesn't start if no email address is present. This prevents feedback loops caused by that account.
Tip from Microsoft: Build the condition in a "Filter array" helper action first. Select values from the trigger there so the expression uses triggerBody(). Copy it from advanced mode into the trigger condition and delete the helper action.
Sources
- Microsoft Learn: Reference guide to workflow expression functions (opens in a new tab) (en-us, Jul 21, 2026)
- Microsoft Learn: Get to know the cloud flows designer (opens in a new tab) (en-us, Jul 16, 2026)
- Microsoft Learn: Use expressions in conditions (opens in a new tab) (en-us, Sep 21, 2026)
- Microsoft Learn: Customize triggers with conditions (opens in a new tab) (en-us, Oct 29, 2026)
- Microsoft Learn: Convert time zone (opens in a new tab) (en-us, Aug 19, 2026)
- Microsoft Learn: Use data operations (opens in a new tab) (en-us, Aug 19, 2026)
- Microsoft Learn: Working with Get items and Get files (opens in a new tab) (en-us)
- Microsoft Learn: SharePoint connector (opens in a new tab) (en-us)
- Microsoft Learn: Use variables in cloud flows (opens in a new tab) (en-us)
- Microsoft Learn: Use OData query operations in SharePoint REST requests (opens in a new tab) (en-us, Oct 28, 2026)
- Microsoft Learn: Excel Online (Business) connector (opens in a new tab) (en-us)
- Microsoft Support: Date systems in Excel (opens in a new tab)
- Microsoft Learn: Excel incorrectly assumes that 1900 is a leap year (opens in a new tab) (en-us, Mar 30, 2026)
- Microsoft Learn: Default time zones in Windows (opens in a new tab)