The runtime value to be converted doesnt have expected format

(0) Share

Report

Posted on by ragavanrajan

7,044 Most Valuable Professional

Hi Folks,

I have the Power Automate flow which converts Excel to SharePoint list. In my Excel File, few columns are date fields. In my flow the following error has been thrown . Doesn't have the expected format date. I can confirm that it is a date field in Excel. I did search many posts but clearly I am not getting output.

I have tried many flow expressions including formatdatetimevalue but no luck

Please help

Categories:

Using flows

I have the same question (0)

All responses (4)

Answers (1)

Sort by

CFernandes 8,504 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@ragavanrajan

Hey Mate, Have a look at https://www.bythedevs.com/post/working-with-date-time-data-of-excel-in-power-automate

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Was this reply helpful? Yes No
ragavanrajan 7,044 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @CFernandes , Thanks for the link. Still I am facing the following error.

I have used the formatdatetime function.

Please help

Was this reply helpful? Yes No
v-alzhan-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @ragavanrajan ,

Could you please share the screenshot of the configuration of your flow so we could better help you and provide the proper workaround?

You could take a try with the expression as below:

addDays('1900-01-01',add(int(43873),-2))

formatDateTime(addDays('1900-01-01',add(int(43873),-2)),'yyyy-MM-dd')

Best Regards,

Alice

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
Verified answer

CFernandes 8,504 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@ragavanrajan As @v-alzhan-msft mentioned the screenshot of your configuration will help us understand your requirement better.

43873 is the number of days between 30-12-1899 to the current date, this is how format Excel stores values added to cells of 'Date' format.

To work around the problem, you will need to apply the formula below.

addDays('1899–12–30T00:00:00Z',int(item()?['YourDateColumnName']))

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Was this reply helpful? Yes No