How to deal with empty date

(0) Share

Report

Posted on by FranciscoVC

Hello everybody.

I'm building a flow to update a Sharepoint List from a Excel.

This excel has some columns with dates, which may contain data or not.

When I created de Sharepoint list there was no problem with that empty cells, but I have problem creating the flow.

This is the code I'm using to update the list:

if(empty(items('For_each')?['ANTIGUEDAD_CARGO']),null,addDays('1899-12-30',int(items('For_each')?['ANTIGUEDAD_CARGO']),'dd/MM/yyy'))

When the cell from excel contains a date works fine, but when it's blank it returns this error:

OpenApiOperationParameterTypeConversionFailed. The 'inputs.parameters' of workflow operation 'Update_element' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/field_10' is required to be of type 'String/date'. The runtime value '""' to be converted doesn't have the expected format 'String/date'.

I've read a lot of pages saying that using null solves the problem but not for me.

Any clues?

Thanks in advance.

Categories:

General topics

I have the same question (0)

All responses (7)

Answers (1)

v-yueyun-msft on at

Like (0)

Report
Hi , @FranciscoVC
You can try to use this code :
if( equals(items('For_each')?['ANTIGUEDAD_CARGO'],string('')) , null , if(empty(items('For_each')?['ANTIGUEDAD_CARGO']),null,addDays('1899-12-30',int(items('For_each')?['ANTIGUEDAD_CARGO']),'dd/MM/yyy')))

Best Regards,
Yueyun Zhang

Was this reply helpful? Yes No
FranciscoVC 10 on at

Like (0)

Report

Sadly doesn't work either

Was this reply helpful? Yes No
Chriddle 8,436 Super User 2025 Season 2 on at

Like (0)

Report
Is the date value in the Sharepoint list "required?"

Then you have to insert a valid date 😉

However, don't do this nasty date calculation

addDays('1899-12-30',int(items('For_each')?['ANTIGUEDAD_CARGO']),'dd/MM/yyy'))

Just set the date format to ISO-8601 in the Get rows action and get valid date strings.

Don't use format 'dd/MM/yyy'

This is not according to ISO-8601 (The format that the Sharpoint connector expects).

If you want to get rid of the values's time, use format 'yyyy-MM-dd'

If not, don't use a format string (it's optional 😉)

Was this reply helpful? Yes No
FranciscoVC 10 on at

Like (0)

Report

No, it's not set to required.

So this would be ok?

if(empty(items('For_each')?['ANTIGUEDAD_CARGO']),null,addDays('1899-12-30',int(items('For_each')?['ANTIGUEDAD_CARGO']),'yyyy-MM-dd'))

Or how can I do it in a better way? This is my flow

First list files from table
Second Get elements
If element exists then update element, if not, create element

Was this reply helpful? Yes No
Verified answer

Chriddle 8,436 Super User 2025 Season 2 on at

Like (0)

Report

The new designer has an issue with the expression null.

Maybe switching to the old designer will help in your case.

https://powerusers.microsoft.com/t5/Building-Flows/Updating-Sharepoint-list-date-column-to-blank/m-p/2572106#M287912

Was this reply helpful? Yes No
FranciscoVC 10 on at

Like (0)

Report

Thank you, I'll give a try

Was this reply helpful? Yes No
FranciscoVC 10 on at

Like (0)

Report

It is the trick, switching to de old designer fixed it. My brain was broken.

Thank you so much mate,

Was this reply helpful? Yes No