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

Update Excel Row by DATE key column

I'd like to update an Excel table based on the key column which is a DATE. I know Excel stores date as int, but how should I supply the parameter to the Excel Update Row connector?

 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Sbence
Level: Powered On

Re: Update Excel Row by DATE key column

I couldn't find a general solution. I work with dates in 2020 only so I use this workaround

 

add(43830, dayOfYear(date_to_look_for)))

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Update Excel Row by DATE key column

Hi @Sbence 

 

I never tried to use an actual date, but I think if you pass the date formatted the same way as it is on Excel, then you'll be able to search for it. 

 

Ideally, you should use strings or better yet numbers to avoid issues, but if you want dates, then we can try this, and if it doesn't work, we can dig-in a little bit further.

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

 

 

Sbence
Level: Powered On

Re: Update Excel Row by DATE key column

"if you pass the date formatted the same way as it is on Excel, then you'll be able to search for it."

 

Thanks, I've tried it, doesn't work this way. It has to be numeric

 

flowexcelupdate.png

 

Super User
Super User

Re: Update Excel Row by DATE key column

HI @Sbence 

 

You can calculate the number. The number 1 is January 01 1900 so we need to check the difference in seconds since that date until today. This is the value that you can send to Excel.

 

There is no easy way to do a difference between 2 dates, but there's another thread that can help you:

https://powerusers.microsoft.com/t5/General-Power-Automate/Calculating-the-difference-between-two-da...

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

 

Sbence
Level: Powered On

Re: Update Excel Row by DATE key column

Yeah, each and every example shows how to calculate the DATE value from the INT (as stored in Excel), but not the opposite. And I'd like to avoid to loop through all the rows in flow

Highlighted
Sbence
Level: Powered On

Re: Update Excel Row by DATE key column

I couldn't find a general solution. I work with dates in 2020 only so I use this workaround

 

add(43830, dayOfYear(date_to_look_for)))

View solution in original post

Helpful resources

Announcements
firstImage

Better Together Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

firstImage

Incoming: New and improved badges!

Look out for new contribution recognition badges coming SOON!

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!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (10,432)