Unable to update a row in Excel Table with Date Timestamp as key values

[SuyogD03]

Hi All,

I have to update a row in the excel table with the date as a key value. I am getting these values in the dd-MMM-yy (10-Apr-21) format. Whatever conversion format I use my flow fails with 'No row was found with Id '2021-04-10T00:00:00.000Z' error.

However, I have this value present in excel. Get rows shows me the data mentioned below:

The error message is as below:

Anyone, can you please help me solving this issue?

@manuelstgomes, I have seen your comment on a similar issue. It will be helpful if you can provide me some hints to fix this issue.

[PhilippeR]

hello quick question

whats the value of a cell in column date in excel ?

2021-04-01 or 2021-04-10T00:00:00.000Z ?

[SuyogD03]

Thanks @PhilippeR,

Yes, the date in the excel is in 2021-04-10T00:00:00.000Z format.

[manuelstgomes]

Hi @SuyogD03 

Data columns are a pain as key columns. What you see (a date formatted based on your locale) in Excel is not what's stored in Excel. It stores dates as numbers. I have a detailed explanation here.

I'm not 100% sure, but when comparing the value from Power Automate (a formatted date) to Excel if it considers the number or the formatted date to do the comparison. I would almost bet that it's the first case.

Do you really need that the date is the key value?

Cheers
Manuel

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

[SuyogD03]

Thanks for the response @manuelstgomes  and sorry about the delay.

Yes, the date is an important key column. I have created a workaround with the numeric key column but I don't think business will be happy in changing to anything else other than the date for a daily reporting process.

I believe, there should be a bridge to convert data (dd-MMM-yy) format to Serial number (Something that excels understand date as ). This might solve the issue too.