Power Automate DateTime Format Error

[anthonjhnon1]

Ok I'm having some sort of unsolvable issue with it comes to my datetime columns. I've tried everything I can think of and nothings working. What's even more frustrating is that I've done this before and it worked in the past. I still have the original automation and I even tried mimicking the entire automation which isn't much. 

I have a manual trigger, Excel: list rows present in table, and SharePoint: Create Item. I've tried with ISO 8601 format and without with data manipulations in a compose statement to verify the values. Even then it says something is wrong. I even tried chat GPT, searched all over the internet and nothing is helping.

Error Message

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

With ISO 8601, I even tried this using an 'or' instead of a nested if.

if(
empty(item()?['Last Worked']),
null,
if(
equals(item()?['Last Worked'], ''),
null,
concat(
substring(item()?['Last Worked'], 0, 10),
'T',
substring(item()?['Last Worked'], 11, 8),
'Z'
)
)
)

Without ISO 8601, I even tried 'null' instead of '1899-12-30'

addSeconds(
addDays('1899-12-30', int(first(split(outputs('LastWorked'), '.')))),
int(
formatNumber(
Add(
mul(
float(concat('0.', last(split(outputs('LastWorked'), '.')))),
86400
)
,1
),
'#0')
)
)

Please help me, this is driving me crazy knowing it worked before.

[v-yetonggu-msft]

Hi @anthonjhnon1 ，

Could you please give me an example of your datetime return value?

Best Regards,

Sunshine Gu

[anthonjhnon1]

There are a lot of null values in this column and I believe that's the problem. Another column I have for time called ['TS'] has a mostly filled data and excel raw input shows as '2024-06-05'.

[anthonjhnon1]

Also right before this 'Without ISO 8601, I even tried 'null' instead of '1899-12-30'  I had a compose statement that would return '0.0' if value was empty(). And it returned '0.0' so I know it was null or empty.

[v-yetonggu-msft]

Hi  @anthonjhnon1 ,

Please select the ISO 8601 format and try this expression:

if(empty(item()?['TS']),null,item()?['TS'])
if(empty(item()?['Last_Worked']),null,item()?['Last_Worked'])

Best Regards,

Sunshine Gu

[anthonjhnon1]

This is what I had in my original automation that worked and I believe I tried this already.
I just tried and still getting the same error message.

[v-yetonggu-msft]

Hi @anthonjhnon1 ,

If you have the wrong column name in your expression, please try the correct column name, and please switch to the classic designer.

if(empty(items('Apply_to_each')?['Last_worked']),null,items('Apply_to_each')?['Last_worked'])

Best Regards,

Sunshine Gu

[anthonjhnon1]

The column name is a good catch. Even thought my excel file doesn't have a '_', Power Automate applied one. Also seems I can't have anything in the 'item()'.

However, I'm still getting the same error message even after using the correct column name.

[v-yetonggu-msft]

Hi @anthonjhnon1 ,

Could you please show me a screenshot of your flow?

I'd like to see what loops are nested on the outside of your create item.

Best Regards,

Sunshine Gu

[anthonjhnon1]

These are not all the columns.

[v-yetonggu-msft]

Hi @anthonjhnon1 ,

感谢您的快速回复。

Note that to reference the parameters in Apply to each, use items('Apply_to_each').

Please try this expression:

if(empty(items('Apply_to_each')?['Last_worked']),null,items('Apply_to_each')?['Last_worked'])

Best Regards,

Sunshine Gu

[anthonjhnon1]

I copied and pasted your expression and still getting the same dang error message. This is so frustrating. I even tried without the '_' in the title due to my Excel table not having it and when I did a download of the ouptut of the Excel action, it didn't show an '_' either in the classic view.

[anthonjhnon1]

Just for context.

[v-yetonggu-msft]

Hi @anthonjhnon1 ,

Please refer to the current Excel Table column name in the expression instead of copying my expression exactly.

if(empty(items('Apply_to_each')?['ColumnName']),null,items('Apply_to_each')?['ColumnName'])

Best Regards,

Sunshine Gu

[anthonjhnon1]

I'm saying I've copied your expression and even tried different variations of the column name. I'm going to edit the column name in the excel to just 'LW'. Standby.

[anthonjhnon1]

Ok I changed the excel column to 'LW' and still getting the same error message which is interesting. Even when I download the excel out put it shows the column as 'LW'. What could this mean?

[anthonjhnon1]

I guess Last_Worked is referencing the SharePoint list and not the excel. 

[v-yetonggu-msft]

Hi @anthonjhnon1 ,

It's so weird that I can run it successfully with this expression.

My Excel Table:

My SharePoint List:

My Flow:

The Result:

This has nothing to do with the SharePoint list, the data source is from excel table, I observed that your column name is preceded by 'item/' prefix, this is the problem referenced in Apply to each, please re-create a flow and test it again.

Best Regards,

Sunshine Gu

[anthonjhnon1]

That's exactly what I was working on before I made this post. I'll do it again and I'll let you know the results.

[anthonjhnon1]

I first tried deleting and adding the actions. Same error message. Then I tried a brand-new flow. Still the same error message.