“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'))
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).
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.