cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

function 'formatDateTime' is Null and Errprs

Hi,

 

I have seen several topics on this question but I can't seem to get any of the solutions to work. I have a Date field that's not required in a SharePoint List. If a date gets entered I want the date to be formatted; if a date is not entered I just want it to output nothing.

 

I keep getting this error: function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'.

 

It seems like the False outcome in the If experssion I made still gets evaluated and throws an error if the condition is true. The 

 

if(equals(triggerBody()?['Test_x0020_Date'],null),'',formatDateTime(triggerBody()?['Test_x0020_Date'],'D'))

 

The Date Column is called "Test Date" in the example above.

 

Thanks in Advance

7 REPLIES 7
sergeluca
MVP

Hello,

 

your syntax is correct, but the reason this fails is (I think) because formatDateTime(triggerBody()?['Test_x0020_Date'],'D') is probably computed (somewhere in a stack) but its result is not applied if 'Test Date' is null. and this crashes  because formateDateTime cannot work if a date is null.

The proof is if your write:

if(equals(triggerBody()?['Test_x0020_Date'],null),'X','Z')      this should work.

I know it is weird..

You can store the value in a variables or just use a normal condition action. 

 

 

 

 

v-xida-msft
Community Support
Community Support

Hi @Anonymous,

 

Could you please share a screenshot of your flow's configuration?

 

The error message told that the formatDateTime function expects its first parameter to be a string, but you provide a Null value.

 

I have made a test on my side and the issue is confirmed on my side. My flow's configuration as below:12.JPG

Inputs set to following formula:

if(equals(triggerBody()?['Test_x0020_Date'],null),'',formatDateTime(triggerBody()?['Test_x0020_Date'],'D'))

The error shows up as below:13.JPG

I think the issue is related to If() function, the True value and False value within If() expression would be calculated at the same time with If Conditional judgment.

 

In addition, you could consider take a try to achieve your needs with following workaround:

  • Add a "When an item is created" trigger, specify Site Address and List Name.
  • Add a Variables-> "Initialize variable" action, Name set to Output, Type set to String and Value set to empty.
  • Add a Condition, click "Edit in advanced mode", type the following formula:
@equals(triggerBody()?['Test_x0020_Date'], null)
  • Within "If/yes" branch of Condition, add a "Set variable" action, Name choose Output and Value set to ''. 
  • Within "If/no" branch of Condition, add a "Set variable" action, Name choose Output and Value set to following formula:
formatDateTime(triggerBody()?['Test_x0020_Date'],'D')

Image reference:14.JPG

 

The flow works successfully as below:

When the Test Date column is not entered:15.JPG

When the Test Date column is entered:16.JPG

 

 

 

Best regards,

Kris

 

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello kris,

 

thank you for your answer;  the IF behavior has been escalated to the LogicApps team;  lazy evaluation will be implemented for future runs because it is more intuitive ( and more efficient) for most users

https://en.wikipedia.org/wiki/Lazy_evaluation

 

Kind regards

 

Serge

 

Anonymous
Not applicable

Thanks @v-xida-msft,

 

The conditional statements may be useful if we just had to check for one null value but I have to check for a number of them. I will instead make the fields required and not proceed unless they are all not null instead. Thanks for the solution.

 

@sergeluca, Lazy evalution will be much appreciated when it comes out.

 

Thanks,

Suj

date-issue.pngdateTime-issue2.png

I am having issue, i am stroing the date values into a varible but is it is not having date "Questions Due Date", i am getting error 400 while saving the field is of type Date only in CRM.
Where as same date as JSON is accepting/showing null and if i am trying to pass null / '' which is not acceptable.
saved_date.png


Please help...

MH3
Post Prodigy
Post Prodigy

Hello @v-xida-msft ,

 

I have this same issue, I have 4 leave types (TimeOff, Annual Leave, Casual, Sick) which user selects and enters the data according to the selected leave type in PowerApps,

I insert the data into SP list by using patch function in which if the user selects LeaveType as "Time Off" then only TimeOff related things are inserted in SP List.

 

Now, I am running a flow whenever an item is created an email is sent to the manager and then he/she accepts or rejects.

 

I have used all the fields in the Start and Wait For Approval Step to show the details whichever leave type is selected.
start.jpg
The First FormatDateTime expression is for Time Off Date, the last two are Start Date and End Date  used in Leave Types (Annual, Casual and Sick).

 

So, when the user posts TimeOff record, the start and end date are null in that record, so my flow shows an error.

and this happens with Annual, Casual and Sick, when the user create this, then TimeOff Date goes null.

 

So, my Flow Run Fails..

 

Any Help? 

labrady
Regular Visitor

Another option (and much easier) is to simply create a calculated column in the SharePoint list that leaves the field blank if null or formats the date if not null. 

Sample formula:
=IF(ISBLANK(DateColumn),"",TEXT(DateColumn,"MM/dd/yyyy"))

This field does not have to be added to a view - and can be added as dynamic content in the flow. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (1,625)