Date from Excel is not ISO 8601 despite choosing option

[AVoraman]

I'm trying to create events on an Outlook calendar from an Excel spreadsheet. I saw other answers here that say to use the ISO 88601 in the Advanced Options for the List rows in a table.  Except, it doesn't seem to work.

As you can see, the dates look like Excel number dates and not ISO 8601, despite my setting a custom format in Excel in addition to opening the workbook with the ISO 8601 option.

I have tried using the formatDateTime() function and that also errors. 

[creativeopinion]

@AVoraman You need to use an expression to format your date. Use the formatDateTime() function.

However, you'll notice that once you insert an expression, the only dynamic content you'll see in the list is body and value. 

To get around this—use this trick. Exit out of the Expression tab. 

Insert the dynamic content for the Start Time into the Start Time field. In my case I'm just using the TaskDueDate column from my Excel table. You'll need to select the appropriate dynamic content in your case. 

Hover over the dynamic content label note the text between the single quotes. This is the dynamic content key. It's case sensitive.

Delete the dynamic content label. Insert an expression. Use the formatDateTime() function.

Insert this between the formatDateTime() function:

item()?['']

Between the single quotes, you'll want to type in the dynamic content key. In my case it's TaskDueDate

Note the timestamp format in the Start time field.

Add a comma and single quotes. Enter this timestamp format between the single quotes:

yyyy-MM-ddTHH:mm:ss

Press OK to insert the expression. 

Repeat this for the End Time field.

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

[AVoraman]

Unfortunately, this did not work.

When I hover the Start item, it shows this: @{items('Apply_to_each')?['Start']}

I'm using this expression: formatDateTime(item()?['Start'],'yyyy-MM-ddTHH:mm:ss') to get the error pictured above.

I tried to figure out how to get all of the important stuff from the hover text into that expression in a way that let me save and test. The only way I was able to get a valid expression was by assigning that and the Finish fields to String variables and building the expressions with the variables. That gets me the same error as pictured above.

When I went to click [Reply] I got this message: "Your post has been changed because invalid HTML was found in the message body. The invalid HTML has been removed. Please review the message and submit the message when you are satisfied." I don't see any changes, so not sure where that came from. 

[creativeopinion]

@AVoraman I would recommend that you use Compose actions to Compose your Start + End Dates first and run a test to ensure the output BEFORE you even add the Create Event action to your flow.

Follow this YT Short tutorial where I cover how to convert the Excel numerical date to a proper date. Remember to rename your actions to keep your flow organized!

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

Once you confirm that your Compose actions are outputting a proper timestamp, insert the outputs of the Compose actions into the Create Event Action.

[AVoraman]

New sparse flow

Same setting for ISO 8601

What else should I try?

[creativeopinion]

@AVoraman Remove the Post message in a chat or channel action. You don't need that right now. You need to first convert your datetime into the proper format. 

You need to define a datetime format in the addDays() function. 

addDays('1899-12-30', int(outputs('Excel_field')), 'yyyy-MM-ddTHH:mm:ssZ')

Run a test. Review the output.

[AVoraman]

Did that.  Same thing.

The text in the error reads: Unable to process template language expressions in action 'Start_-_date' inputs at line '0' and column '0': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

It's saying the output (in this case 45236.7083333333) cannot be converted into an int. It's not even getting to the addDays() so lacking a formatting string doesn't matter at this point.

What should I test next?

[creativeopinion]

@AVoraman Can you screenshot what your timestamp in Excel looks like? In the cell?

[creativeopinion]

@AVoraman I believe your issue might be with the format you've used in Excel. Can you try to use either a format from the Date or Time category?

By selecting one of these options the output of the dynamic content from Excel should return a proper timestamp. 

[AVoraman · Accepted solution]

The hot ticket is to use

formatNumber(float(<field>),"0")

and feed that to 

addDays('1899-12-30',int(<output from above>),'yyyy-MM-ddTHH:mm:ss')

Because these are always all-day (so they show as Free at the top of the calendar), if the start and end are the same, I add a day to the end-date by using 1899-12-31 in the formula. 

I found the formatNumber() by using the Format data by example and plugging in 45142.3333333333 and 45142.