Get a row using date column in Excel

[Anonymous]

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?

[efialttes]

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

[Jcook]

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

[Anonymous]

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

[Anonymous]

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

[Jcook]

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

[efialttes]

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

[Anonymous]

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

[Anonymous]

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

[efialttes · Accepted solution]

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.

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