“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).
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.
You have the coalesce expression to avoid empty/optional inputs and avoid this.
You have the coalesce expression to avoid empty/optional inputs and avoid this. Hsjasj
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’))
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?
Hello Kathy,),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.
empty(…) should be the right formula to use, if(empty(
There is no way to create more than 8 conditions. But, not found proper solution here to overcome this issue.
Hello Sami,
you can change the design of the flow or of the expression if you need more than 8 conditions, e.g. split it into 2 high level conditions where each of them can continue with the next 7 conditions = 15 in total.
I am running into a situation where I need to “reset” the date in a column(date/time column). I can’t seem to use a ‘null’ expression as I get the same error as above. So I guess my question is, how do you clear the date that is in a cell with powerautomate?