cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BurritoFoot
Regular Visitor

Convert Flow String Variable from Date to Serial Date for Excel Online Matching

I'm new to flows, setup a flow that grabs some key fields from email subject/body into variables and sends them in a seprate email, all looks fine.


I'd like to use the same variables to update a row in excel but to start with I'm using 'Get a row' to check it matches OK.


Problem is that I want to use the Date column in the excel table as the key column and it won't match to the flow variable that has the date in, because excel is returning serial dates and not dd/mm/yyyy format dates.


I've read about converting serial dates to dd/mm/yyyy format dates but couldn't find anything on converting dd/mm/yyyy format dates to serial dates. I'm assuming I need find the difference in days between 30-12-1899 and the date I want to match on so that I'm matching to the excel field using a serial date?

Is anyone able to offer some pointer on the syntax for this? I've done some searching for date subtraction but haven't been able to figure it out!


Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions

I've resolved this by splitting the date, grabbing each element (year, month, day) into separate compose's and then using concat to assemble those in yyyy-mm-dd format and performing the maths on that date.

 

Pretty sure I've made this harder than it needs to be but I hit errors when I tried to use concat against the split elements of the string, all in one expression so I had to work this way.

 

That part of the flow is now working, so on to the next issue.

View solution in original post

4 REPLIES 4
BurritoFoot
Regular Visitor

Think I'm on my way to a fix, did some more searching for finding the difference in days between to dates and tried this

div(sub(ticks('2020-10-09'),ticks('1899-12-30')),864000000000)

 

Which I shamelessly stole from https://powerusers.microsoft.com/t5/Building-Flows/Number-of-days-between-two-Dates/m-p/281584#M2919...

 

That works and I now match OK on date field, although at the moment this is just a static date.

So it looks like I need to split and flip my UK format dates to US format, apply that formula and then use the result to search.

 

Seems complicated for such a simple operation -can't help thinking I'm missing a simpler solution to this!

I've resolved this by splitting the date, grabbing each element (year, month, day) into separate compose's and then using concat to assemble those in yyyy-mm-dd format and performing the maths on that date.

 

Pretty sure I've made this harder than it needs to be but I hit errors when I tried to use concat against the split elements of the string, all in one expression so I had to work this way.

 

That part of the flow is now working, so on to the next issue.

View solution in original post

ham
Community Support
Community Support

Hello @BurritoFoot ,

 

We are glad that were able to find the solution for this. 

To convert the time zone use convertTimeZone function.

 

Best Regards,

Harish M

Please mark as verified if the answer is helpful.

Thanks Harish, I thought I must be missing a simpler way to do it, I'll give that a go.

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,052)