get excel data time is wrong

[Anonymous]

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

[RezaDorrani]

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

[yashag2255]

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!

[Anonymous]

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

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

[v-litu-msft · Accepted solution]

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.

[Anonymous]

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

[v-litu-msft]

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.