Answered

get excel data time is wrong

Like (0) Share

Report

Posted on 14 Sep 2019 13:48:49 by Community Power Pla...

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 ??

All responses (6)

Answers (1)

v-litu-msft on 16 Sep 2019 at 08:45:57

Like (0)

Report

Re: get excel data time is wrong

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.
Community Power Pla... on 16 Sep 2019 at 08:16:04

Like (0)

Report

Re: get excel data time is wrong

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 ???
Verified answer

v-litu-msft on 16 Sep 2019 at 06:10:42

Like (2)

Report
Re: get excel data time is wrong

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.
Community Power Pla... on 16 Sep 2019 at 01:37:36

Like (0)

Report

Re: get excel data time is wrong

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

After I use the method, in flow ,This situation is still the same
yashag2255 24,584 Super User 2024 Season 1 on 15 Sep 2019 at 12:48:49

Like (0)

Report

Re: get excel data time is wrong

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!
RezaDorrani 12,141 on 15 Sep 2019 at 04:54:28

Like (0)

Report

Re: get excel data time is wrong

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