cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JACK_LAI
Level: Powered On

get excel data time is wrong

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: get excel data time is wrong

Hi @JACK_LAI,

 

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')

Annotation 2019-09-16 140407.png

This is the result of runs:Annotation 2019-09-16 140353.png

 

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.

 

View solution in original post

6 REPLIES 6
Dual Super User
Dual Super User

Re: get excel data time is wrong

Hi @JACK_LAI 

 

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

 

Dual Super User
Dual Super User

Re: get excel data time is wrong

Hi @JACK_LAI 

 

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!

JACK_LAI
Level: Powered On

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

 

1.JPG

 

2.JPG

Highlighted
Community Support Team
Community Support Team

Re: get excel data time is wrong

Hi @JACK_LAI,

 

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')

Annotation 2019-09-16 140407.png

This is the result of runs:Annotation 2019-09-16 140353.png

 

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.

 

View solution in original post

JACK_LAI
Level: Powered On

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

 

3.JPG

Community Support Team
Community Support Team

Re: get excel data time is wrong

Hi @JACK_LAI,

 

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.Annotation 2019-09-16 164348.png

 

Annotation 2019-09-16 164531.png

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.

Helpful resources

Announcements
firstImage

Power Platform Online Conference

Speakers, submit your sessions now! Call for speakers ends Feb. 10!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (5,037)