Answered

get excel data time is wrong

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

In excel column is date format “yyyy/MM/dd”, but after flow to get my excel data ,it will become "DATE_LAB": "43691.3573611111" .

So how can I modify my flow to let me get the right value ??

Categories:

Using flows

I have the same question (0)

All responses (6)

Answers (1)

Sort by

RezaDorrani 12,145 on at

Like (0)

Report
Copy link

Link copied!

Hi @Anonymous

Excel date columns are converted to numbers in Flow - this is by design

An option would be to create a text column in excel for the date field(mm/dd/yyyy format) and then in Flow you can use
convertToUtc(variables('date'),'Pacific Standard Time') and convert your string field value into UTC from your base time zone and use in Flow

Regards,
Reza Dorrani

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
yashag2255 24,769 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @Anonymous

This is a known issue with the excel connector. There are two ways you can address this:

1. As suggested by @RezaDorrani convert the column (you will have to select the entire column) and then cange the datatype to plain text and then use the format date time function in flow.

2. refer to the formula provided in this solved thread to directly get the values in Flow https://powerusers.microsoft.com/t5/Building-Flows/Excel-dates-turned-into-integers/td-p/130364

Hope this Helps!

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
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

This is my excel file ........

After I use the method, in flow ,This situation is still the same

Was this reply helpful? Yes No
Verified answer

v-litu-msft Microsoft Employee on at

Like (2)

Report
Copy link

Link copied!
Hi @Anonymous,

Since the return value is Float, so you need to process the Float into an integer, we can use the split function to separate it into integer and decimal parts. Then get the integer part to put into the expression:

int(split(string(outputs('Compose')),'.')[0])

addDays('1899-12-30',outputs('Compose_2'),'yyyy-MM-dd')

This is the result of runs:

I hope it can help you.

Best Regards,
Community Support Team _ Lin Tu
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
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

thank you very much @v-litu-msft

After your method , I can get right value. But when I use this in condiction ,even though compse3 is '2019-09-116' and utnNow('yyyy-MM-dd') is 2019-09-16 ,the result is false..........

How can I compare this string ???

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

Like (0)

Report
Copy link

Link copied!

Hi @Anonymous,

I have tested on my side, but not find the same issue, please confirm the formate of the utcNow is same as the output of Compose action.

Best Regards,
Community Support Team _ Lin Tu
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