cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Get a row using date column in Excel

Hello,

 

I am simply trying to use the Get a Row function to get information from a table using a date. Even though it shows that the right date is being used in the Get a Row function, it throws an error stating that that the row cannot be found.

 

What am I not doing right?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Dual Super User
Dual Super User

Re: Get a row using date column in Excel

Hi again

I created a test Excel with a DATE format column and four rows:

TestingDate
13/02/2020
13/02/2019
05/12/2019
05/12/2020

 

I have created a test PA Flow to read it, and verified all these example dates are shown in Flow as integers that are internally converted into strings:

[
"43874",
"43509",
"43804",
"44170"
]

In order to get row whose Key is 13/02/2020 I first 'create' this date in flow format ('Compose' action block), then I convert it into Excel Date format ('Excel Get a row' action block) by applying the suggested workaround. Remember it is only valid for 2020 dates, since 43820 equals to 31st Dec 2019.

 

Flow_ExcelDates.png

 

 

string(add(43830,dayOfYear(outputs('Compose_2020Feb12'))))

 

 

My PA Flow execution succeeds, and I get the expected row as output.

 

If you need this workaround to work no matter the year, you will probably need to calculate the nr of days between the desired date and the one Excel uses as start point to generate the integer values. According to this post, the date calendar in Excel starts on January 1st, 1900. Please also note, that, according to this web site:

 


Days Since 1900-01-01 (+2) is used in the Microsoft Excel date functions such as DAY(serial_number) and WEEKDAY().

Note, Excel's serial number is 2 higher than the number on this page.

  • In Excel January 1 is serial number 1. In this converter midnight January 1, 1900 is 0, after 1 day it is midnight on January 2. To correct this you have to add/subtract 1.
  • Excel incorrectly sees 1900 as a leap year (for compatibility with Lotus 1-2-3) so you have to add/subtract 1 to all days when using in Excel. Also see: Excel incorrectly assumes that the year 1900 is a leap year

Finally, this thread explains how to calculate in Flow the nr of days between two dates, they use the following example:

 

div(sub(ticks('2019-05-13'),ticks('2019-05-04')),864000000000)

Hope this helps

 

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

9 REPLIES 9
Highlighted
Dual Super User
Dual Super User

Re: Get a row using date column in Excel

Hi
Can you share a screenshot of your flow definition, columns of your excel and details of action block Get a Row inputs?
Thanx!


Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Super User
Super User

Re: Get a row using date column in Excel

Hello,

Try doing a list rows without a filter to see what comes back.

If excel is using a date column than this could be the issue as excel passes dates as serial dates.

To convert a serial date use the addDays expression. Here is an example:

I put the serial date in a compose action in flow, with the name Compose Due Date
Than I use another compose action to convert the date. In my case this is what my expression looks like:
addDays('1899-12-30',int(outputs('Compose_Due_Date')),'MM/dd/yyyy')

Hope this helps!
--Josh

If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others




Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up ?


Proud to be a Flownaut!





Highlighted
Helper II
Helper II

Re: Get a row using date column in Excel

@Jcook  Is there a way to do it backwards so say someone selects a date when triggering the button flow, that i can convert the date they selected into the excel serial date so i can use that as the key value for a column?

Highlighted
Helper II
Helper II

Re: Get a row using date column in Excel

Spreadsheet.JPGOpen Flow.JPGError.JPG

 

@efialttes This is what is currently happening and what i am trying to do.

Highlighted
Super User
Super User

Re: Get a row using date column in Excel

Hello,

I have not found a easy way to convert the string date into serial for excel.

May I suggest you use the list records action and use a filter on the user selected date.

I believe the issue is excel is expecting a unique id for the key. And I don’t think excel likes date for the key..

Try list records and use the filter as user selected date.

--Josh

If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others




Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up ?


Proud to be a Flownaut!





Highlighted
Dual Super User
Dual Super User

Re: Get a row using date column in Excel

Hi
Is your key colummn formatted as DATE in excel? If so, this thread can be useful
https://powerusers.microsoft.com/t5/General-Power-Automate/Update-Excel-Row-by-DATE-key-column/td-p/...
Hope this helps


Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Helper II
Helper II

Re: Get a row using date column in Excel

@efialttes  I was hoping to use the date column in Excel, I suppose I could try retyping all the dates as text. Would that make a difference?

 

In that thread you showed me their solution was to use this
add(43830, dayOfYear(date_to_look_for)))

However I think i must be misusing it because I cannot get it to accept the expression I am trying to do?

Highlighted
Helper II
Helper II

Re: Get a row using date column in Excel

@Jcook  I tried to list all rows and then attempted to filter by date but the filter array shows the same serial dates

Highlighted
Dual Super User
Dual Super User

Re: Get a row using date column in Excel

Hi again

I created a test Excel with a DATE format column and four rows:

TestingDate
13/02/2020
13/02/2019
05/12/2019
05/12/2020

 

I have created a test PA Flow to read it, and verified all these example dates are shown in Flow as integers that are internally converted into strings:

[
"43874",
"43509",
"43804",
"44170"
]

In order to get row whose Key is 13/02/2020 I first 'create' this date in flow format ('Compose' action block), then I convert it into Excel Date format ('Excel Get a row' action block) by applying the suggested workaround. Remember it is only valid for 2020 dates, since 43820 equals to 31st Dec 2019.

 

Flow_ExcelDates.png

 

 

string(add(43830,dayOfYear(outputs('Compose_2020Feb12'))))

 

 

My PA Flow execution succeeds, and I get the expected row as output.

 

If you need this workaround to work no matter the year, you will probably need to calculate the nr of days between the desired date and the one Excel uses as start point to generate the integer values. According to this post, the date calendar in Excel starts on January 1st, 1900. Please also note, that, according to this web site:

 


Days Since 1900-01-01 (+2) is used in the Microsoft Excel date functions such as DAY(serial_number) and WEEKDAY().

Note, Excel's serial number is 2 higher than the number on this page.

  • In Excel January 1 is serial number 1. In this converter midnight January 1, 1900 is 0, after 1 day it is midnight on January 2. To correct this you have to add/subtract 1.
  • Excel incorrectly sees 1900 as a leap year (for compatibility with Lotus 1-2-3) so you have to add/subtract 1 to all days when using in Excel. Also see: Excel incorrectly assumes that the year 1900 is a leap year

Finally, this thread explains how to calculate in Flow the nr of days between two dates, they use the following example:

 

div(sub(ticks('2019-05-13'),ticks('2019-05-04')),864000000000)

Hope this helps

 

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

Helpful resources

Announcements
firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

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!

Top Solution Authors
Top Kudoed Authors
Users online (6,959)