Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Triggers
  • Resources
  • ABOUT ME
  • Get help with your flow
Menu

How to format a date that can be empty (null) in Power Automate

Posted on April 21, 2021August 23, 2022 by Tom

“The Power Automate flow works fine if there’s a value for the date in Sharepoint, however, if it’s empty it’ll fail while trying to format the date.”

The template language function ‘formatDateTime’ expects its first parameter to be of type string. The provided value is of type ‘Null’.


When you use the formatDateTime(…) expression in Power Automate, you can’t format an empty date. The input of this expression must be always a valid string with a date, it can’t be ‘null’. It’s not a problem in situations where the date is a mandatory field or when users always enter the date, but what if somebody doesn’t? It can be a simple flow that will fail to create a task because of a missing due date. A reporting flow that will fail because one out of many items has an empty date. Instead of the desired process outcome you get just the error message above.

That’s why you should always check if a date is not empty before you format it, unless you’re 100% sure that it can’t be empty.

Check if date is empty before you format it

Similar to the ‘Condition’ action, Power Automate has also an expression with the same functionality. With the if(…) expression you can define a condition and what should happen if the condition is true or false.

Note: <…> is always a placeholder, replace it including the < and > characters.

if(<condition>, <ifConditionIsTrue>, <ifConditionIsFalse>)

Starting with the <condition> part, that’s where you check if the date is empty. If you enter the date into empty(…) expression, it’ll return true for an empty date, and false if the date is not empty.

if(empty(<dynamicContentWithDate>), <ifConditionIsTrue>, <ifConditionIsFalse>)

e.g. a date from a SharePoint item
if(empty(triggerOutputs()?['body/Date']), <ifConditionIsTrue>, <ifConditionIsFalse>)

The second part is the <ifConditionIsTrue>. What should happen if the condition is true, if the date is empty? The answer is… nothing. If the date is empty, don’t do anything, return an empty value: null. No expression, no single quotes around it, just the string null.

if(empty(<dynamicContentWithDate>), null, <ifConditionIsFalse>)

e.g. a date from a SharePoint item
if(empty(triggerOutputs()?['body/Date']), null, <ifConditionIsFalse>)

The third (and last) part is to define what should happen when the condition is false. That’s the place to format the date. You already checked that the date is not empty, you can format it. Don’t forget that it must be the same date you used in the condition.

if(empty(<dynamicContentWithDate>), null, formatDateTime(<dynamicContentWithDate>))

e.g. a date from a SharePoint item
if(empty(triggerOutputs()?['body/Date']), null, formatDateTime(triggerOutputs()?['body/Date'],'yyyy-MM-dd'))
format empty (null) date in Power Automate

And that’s the whole condition. Instead of using formatDateTime(…) directly, you apply it only on the non-empty dates. In the other situations it’ll skip the formatting (as there’s nothing to format anyway).

Summary

Every time you formatDateTime(…) in your flow, make sure that the date in the expression has a value. If you can’t confirm that, it’s better to check it using the if(…) expression before you start formatting the date. It takes just one item with empty date to break a SharePoint items report or to miss a new entry from MS Forms.

Check if the date is empty, and apply expressions only if it’s not. You’re not limited only to the formatDateTime(…) expression, you can add calculations too, just don’t use calculations in the <condition>! All the additional expressions should be only in the ‘if true’ or ‘if else’ part of the expression.

There’s also possibility to preprocess the data by filtering out the empty dates with a Filter Query (in SharePoint). It’s another solution that can help you avoid the empty dates, but each of them is different. Filtering out the empty dates will completely remove the items, if(…) expression will keep them in the list, it’ll just skip the date formatting or saving.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

5 thoughts on “How to format a date that can be empty (null) in Power Automate”

  1. Nansn says:
    April 27, 2021 at 11:16 pm

    You have the coalesce expression to avoid empty/optional inputs and avoid this.

    Reply
  2. Nansn says:
    April 27, 2021 at 11:16 pm

    You have the coalesce expression to avoid empty/optional inputs and avoid this. Hsjasj

    Reply
  3. Amy G says:
    June 29, 2021 at 3:33 pm

    FYI to help anyone else that comes by the coalesce worked for me for amounts but not dates. For dates, the body JSON needs “2021-06-29” and null must be the word null without quotes. I created a varNull that was equal to null and a variable with the “date” in quotes then used this in my JSON body field.
    if(empty(items(‘Apply_to_each’)?[‘ReimbursedDate’]),variables(‘varNull’),variables(‘varReimbDate’))

    Reply
  4. K.Promma says:
    November 23, 2022 at 6:49 pm

    This is what I need, but for the formatnumber function. It unfortunately doesn’t like the “empty” in the formula. Do you know a solution for checking if the number is empty before formatting it?

    Reply
    1. Tom says:
      December 5, 2022 at 7:57 pm

      Hello Kathy,
      empty(…) should be the right formula to use, if(empty(),null,formatNumber(…)). If it doesn’t work I’d add a ‘Compose’ action before the formula and store the number in there, just to be really sure that it’s really empty.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Do you know what to do, but not how to do it?

Get The Ultimate Power Automate expressions cheat sheet and translate your thoughts into flows with ease!


There's also the HTTP requests to SharePoint cheat sheet to help you overcome limitations of some standard actions!

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

  • How to find Excel rows missing in SharePoint list (Power Automate)March 29, 2023
  • Check whether user exists in AAD with Power AutomateMarch 26, 2023
  • How to highlight whole SharePoint row based on calculated dateMarch 22, 2023
  • Why is your Power Automate flow running so slow?March 19, 2023
  • How to add multiple links to the Power Automate approval taskMarch 15, 2023

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2023 Let's POWER Automate | Theme by SuperbThemes