Answered

DateTime fields are seen as numbers not dates

(0) Share

Report

Posted on by fhtech

I have a spreadsheet that contains three dates in the following format: m/d/yyyy h:mm:ss AM/PM. My flow uses LIST ROWS PRESENT IN TABLE to get the data and then CREATE ITEM to add the data to the SP List. Each Item is mapped, but when the flow is tested, I get the following error:

String was not recognized as a valid DateTime.

Flow shows the output as follows, and notice date is brought in as a number not the date:

9-21-2019 2-28-15 AM.jpg

Any ideas the FormatDate Time as an expression but it did not work as talked about on the following link. Not sure what I am doing wrong.

Does anybody know what this is?

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

ScottShearer 25,270 Most Valuable Professional on at

Like (0)

Report

@fhtech

The issue is that Excelstores dates differently than what Flow expects.

Hrere is a link to a post that discusses this issues as well as a solution.

Was this reply helpful? Yes No
Verified answer

v-alzhan-msft on at

Like (1)

Report
Hi @fhtech ,

Please refer to link below to convert the string to date in flow:

https://powerusers.microsoft.com/t5/Building-Flows/Excel-Online-Date/td-p/134200

And the expression should be as below:

addDays('1900-01-01T00:00:00Z',sub(int(item()?['TheDateColumnTitle']),2))

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
fhtech 30 on at

Like (0)

Report

Hi Alice,

thanks for replying. I tried your suggestion and am getting a different error now:

The string was not recognized as a valid DateTime. There is an unknown word starting at index 0.

Here is the function I used:
addDays('1900-01-01T00:00:00Z',sub(int(item()?['ContractorAssignDate']),2))

Here is what I am seeing now:
Thanks, Reba

Was this reply helpful? Yes No
fhtech 30 on at

Like (0)

Report

Disregard last message. It worked and I accepted the Solution.

Thanks Alice!

Was this reply helpful? Yes No
Sravya_Rajarapu 6 on at

Like (0)

Report

exporting the file from paginated report to power automate the date time column in the report looks like 7/8/2024 10:00 PM and in the flow it looks like 45481.91666666667, how can this be converted? I have tried the Solution in this but that dint work.

Was this reply helpful? Yes No