Answered

'formatDateTime', the value provided for date time string '45657.9999884259' was not valid.

(1) Share

Report

Posted on by HG-24070956-0

Hello,

i have a problem with my date columns. I formatted my excel by python in this format: ".dt.strftime('%Y-%m-%d 00:00:00')"

Now for 2 of 4 columns it works to format this datetime in my powerautomate flow with this function, before i write it inrto my list: "formatDateTime(variables('Zuletzt geändert am_2'), 'MM/dd/yyyy')"

But for the other two ones i still get this error, not matter whatever i tried:

"'In function 'formatDateTime', the value provided for date time string '45657.9999884259' was not valid. The datetime string must match ISO 8601 format.'"

I don't know why but in normal excel opening and also if i convert the file into csv and take a look at notepad, the date look still like this everywhere: "2024-03-08 00:00:00"

But i seems like the excel connector of powerautomate convert this 2 columns everytime into numbers before it works with it...

Hope someone could help me with this, looking for almost 1 week for a solution and don't have ideas anymore...

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (1)

Expiscornovus 33,195 Most Valuable Professional on at

Like (1)

Report
You can use the addDays with 30-12-1899 as a start date if you are only interested in the date portion?

https://tachytelic.net/2020/11/convert-excel-dates-power-automate/

Try something like below

addDays('1899-12-30', int(formatNumber(45657.9999884259, '0')), 'MM-dd-yyyy')

Was this reply helpful? Yes No
ZickQian 2 on at

Like (1)

Report

1. make sure formatting in Excel for the columns is Date format, you can right click the cells->format cells , then check formatting

2. How did you set the variables('Zuletzt geändert am_2') ? Could you send screen copy ?

Was this reply helpful? Yes No
siwfy Microsoft Employee on at

Like (1)

Report

Hi there. You mentioned Excel in the post, I am assuming you are using "List rows present in a table" from Excel Online (Business) connector, right? If yes, the probable underlying cause to this is that Excel stores date values in serial numbers, which are numerical values. However, Power Automate processes date values in ISO8601 format, which are string values. When Power Automate fetches the dates from Excel, they are treated as serial numbers by default. You can switch the dates to ISO8601 format by selecting it in "DateTime Format" option in "List rows present in a table" action. Now you can use formatDateTime() to format the datetime.

Was this reply helpful? Yes No
Verified answer

HG-24070956-0 13 on at

Like (1)

Report

@swify, yes i already used the datetime format option and choose the ISO 8601 Option

doesn't work if i format the column in excel into a data format.

But I solved it another way. I no longer tried to insert the content of my auxiliary variable. I use this to see if there is an empty value in this column of the entry. After this is false, I take the value directly from Excel and do not take the detour via the assigned value of the variable. For some reason this works, although it is the same value that was assigned to the variable.

Was this reply helpful? Yes No