Expression to pass blank dates from Excel to SharePoint list

[third_learner]

Hi. Seeking your help on how to modify my expression below. I want to modify the expression in order to avoid the failure of the flow in the 'Create item' action if the field in Excel is blank. If the date field in Excel is blank, return blank in the SharePoint list.

if(equals(items('Apply_to_each_2')?['Resources Assigned Date'],''),null,addDays('1899-12-30',int(items('Apply_to_each_2')?['Resources Assigned Date']),'yyyy-MM-dd'))

Thank you very much for your help.

[creativeopinion]

@third_learner  You need to make a small adjustment.

if(equals(items('Apply_to_each_2')?['Resources Assigned Date'],''), 
null, 
addDays('1899-12-30', int(items('Apply_to_each_2')?['Resources Assigned Date']), 'yyyy-MM-dd'))

For more flow troubleshooting tips—check out this YT Tutorial: 5 Power Automate Troubleshooting FAQs and Helpful Tips for Creating Better Flows

In this tutorial I cover:

✅ How to troubleshoot a false Condition action result

✅ How to get dynamic content when it isn’t selectable from the list of dynamic content

✅ How to troubleshoot an Apply to Each action that isn’t looping through

✅ How to troubleshoot a skipped Apply to Each action

✅ How to troubleshoot a Filter Query

✅ How to use a SharePoint yes/no column in a Filter Query

✅ How to use Compose actions to troubleshoot a Power Automate flow

✅ How to troubleshoot multiple emails being sent

✅ How to troubleshoot multiple Teams messages being sent

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response! 

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

[third_learner]

Hi @creativeopinion 

I tried your expression but I still got this error. 

By the way, In Excel this is the date field

[creativeopinion · Accepted solution]

@third_learner What's the output of the items('Apply_to_each_2')?['Resources Assigned Date'] dynamic content? Can you insert that Dynamic content into a Compose action and screenshot the output?

Can you also confirm you've selected ISO 8601 as the DateTime format in the List Rows present in a table action?

If you have set the DateTime Format as ISO 8601, this expression should work:

if(equals(items('Apply_to_each_2')?['Resources Assigned Date'], ''), null,items('Apply_to_each_2')?['Resources Assigned Date'])

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response! 

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

Take a look a this YT Tutorial I recently uploaded: 3 Mistakes YOU 🫵 are Making with the Apply to Each Action in your Microsoft Power Automate Flow

In this video tutorial I’ll go over how to avoid these common mistakes when using the Apply to Each action in a Power Automate flow:

1️⃣ Looping through a Single Item

2️⃣ Creating Unnecessary Nested Loops

3️⃣ Looping through an Unfiltered Array

At the end of the video I share a few helpful insights when it comes to using the Apply to Each action in your flow.

I'll also cover:

✅ How to avoid the Apply to Each action with a single item array

✅ How to use the item() function to access dynamic content in an array

✅ How to prevent unnecessary nested Apply to Each action loops

✅ How to use the Select action

✅ How to convert an array to a string with the Select action

✅How to use the Filter Query field

✅ How to count the number of items in an array

✅ How to use a condition control

✅ How to use the concurrency control

✅ How to set a top count

✅ How to use Compose actions for troubleshooting

[third_learner]

Hi @creativeopinion 
If I use my formula, this is the output of dynamic content. Please note that the data field in Excel has a formula

[creativeopinion]

@third_learner What does your dynamic content return when your date field is blank?

[third_learner]

In 'List rows present in a table action' this is the output before it will proceed to the Create item action

If gives an error (screenshot above) if the dynamic value is blank.

[third_learner]

Regarding the ISO 8601, I think it will give me a yyyy-mm-dd format. My expression is converts the date field to dd-mm-yyyy.

[creativeopinion · Accepted solution]

@third_learner In Excel, is the date column blank? Or does it display #VALUE!?

If it displays #VALUE then the expression has to be adjusted to see if the column is equal to #VALUE! and not ''.

Can you insert the dynamic content of the date column into a Compose action (as shown below) and screenshot the output of the Compose action when the date field is empty?

If the Compose action returns #VALUE! adjust your expression.

if(contains(items('Apply_to_each_2')?['Resources Assigned Date'], '#VALUE!'), 
null, 
items('Apply_to_each_2')?['Resources Assigned Date'])

If you don't select ISO8601, Excel will return the date in numerical format. To format your date, you need to use a formatDateTime() function. The addDays() function isn't required if you set the DateTime format to ISO8601

if(contains(items('Apply_to_each_2')?['Resources Assigned Date'], '#VALUE!'), 
null, 
formatDateTime(items('Apply_to_each_2')?['Resources Assigned Date'], 'dd-MM-yyyy'))

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response! 

👉Watch my tutorials on YouTube
👉Tips and Tricks on TikTok

[third_learner]

Hi @creativeopinion 

This formula that you suggested below still gives me the same error if the date field in Excel is blank. I have also selected ISO 601

This is the format in Excel if it has a date value in dd-mm-yyyy format.

[creativeopinion]

@third_learner Can you post a screenshot of the output of the Compose action with the dynamic content when the Excel field is blank? 

Regarding the date format. I may have assumed you wanted to convert the date format to dd-mm-yyyy. If that isn't the case you don't need the formatDateTime() function.

[third_learner]

Hi @creativeopinion 

Is this this correct if I remove the formatDateTime?

if(contains(items('Apply_to_each_2')?['Resources Assigned Date'], '#VALUE!'), 

null, (items('Apply_to_each_2')?['Resources Assigned Date'])

[third_learner]

Hi @creativeopinion 

Thank you for your help and guidance. It works now. 

[third_learner]

Hi @creativeopinion 

This is the screenshot of the dynamic value if the field in Excel  is blank

I used your formula below but I got an error. Asking again for your help. Thank you very much.

if(contains(items('Apply_to_each_2')?['Resources Assigned Date'], '#VALUE!'), null, items('Apply_to_each_2')?['Resources Assigned Date'])

[creativeopinion]

@third_learner It looks like you are missing the formatDateTime() function which I noted in a previous post. 

if(contains(items('Apply_to_each_2')?['Resources Assigned Date'], '#VALUE!'), 
null, 
formatDateTime(items('Apply_to_each_2')?['Resources Assigned Date'], 'dd-MM-yyyy'))

[third_learner]

Hi @creativeopinion 

If the ISO 8601 is turned on, formatDateTime so no longer needed, is that right?

[creativeopinion]

@third_learner I believe you will still need to use the formatDateTime() function to output the date in your specified format. You can test it without the formatDateTime() function. If the date doesn't output as expected—add it back into your expression.

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response! If you're feeling generous— ☕️  Buy me a coffee: https://www.buymeacoffee.com/acreativeopinion

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok