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?
Solved! Go to Solution.
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)))
Hi @Anonymous
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
"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
HI @Anonymous
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:
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
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
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)))
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.
At the monthly call, connect with other leaders and find out how community makes your experience even better.
User | Count |
---|---|
21 | |
20 | |
9 | |
7 | |
7 |
User | Count |
---|---|
33 | |
31 | |
24 | |
21 | |
11 |