Expression to pass blank dates from Excel to SharePoint list

Like (0) Share

Report

Posted on 17 Jan 2024 03:49:02 by third_learner

113

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.

I have the same question (0)

All responses (16)

Answers (2)

creativeopinion 10,480 Super User 2025 Season 2 on 17 Jan 2024 at 04:00:00

Like (0)

Report
Re: Expression to pass blank dates from Excel to SharePoint list

@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

Was this reply helpful? Yes No
third_learner 113 on 17 Jan 2024 at 04:11:01

Like (0)

Report

Re: Expression to pass blank dates from Excel to SharePoint list

Hi @creativeopinion
I tried your expression but I still got this error.

By the way, In Excel this is the date field

Was this reply helpful? Yes No
Verified answer

creativeopinion 10,480 Super User 2025 Season 2 on 17 Jan 2024 at 04:13:04

Like (2)

Report
Re: Expression to pass blank dates from Excel to SharePoint list

@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

Was this reply helpful? Yes No
third_learner 113 on 17 Jan 2024 at 04:33:32

Like (0)

Report

Re: Expression to pass blank dates from Excel to SharePoint list

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

Was this reply helpful? Yes No
creativeopinion 10,480 Super User 2025 Season 2 on 17 Jan 2024 at 04:40:17

Like (0)

Report

Re: Expression to pass blank dates from Excel to SharePoint list

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

Was this reply helpful? Yes No
third_learner 113 on 17 Jan 2024 at 04:46:06

Like (0)

Report

Re: Expression to pass blank dates from Excel to SharePoint list

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.

Was this reply helpful? Yes No
third_learner 113 on 17 Jan 2024 at 04:52:06

Like (0)

Report

Re: Expression to pass blank dates from Excel to SharePoint list

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.

Was this reply helpful? Yes No
Verified answer

creativeopinion 10,480 Super User 2025 Season 2 on 17 Jan 2024 at 04:52:20

Like (1)

Report
Re: Expression to pass blank dates from Excel to SharePoint list

@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

Was this reply helpful? Yes No
third_learner 113 on 17 Jan 2024 at 05:17:43

Like (0)

Report

Re: Expression to pass blank dates from Excel to SharePoint list

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.

Was this reply helpful? Yes No
creativeopinion 10,480 Super User 2025 Season 2 on 17 Jan 2024 at 05:22:05

Like (0)

Report

Re: Expression to pass blank dates from Excel to SharePoint list

@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.

Was this reply helpful? Yes No