Compare excel data with actual date in Power Automate

%3CLINGO-SUB%20id%3D%22lingo-sub-1677651%22%20slang%3D%22en-US%22%3ECompare%20excel%20data%20with%20actual%20date%20in%20Power%20Automate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1677651%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20list%20of%20all%20upcoming%20Teams%20live%20events%20with%20date%20and%20time%20within%20an%20excel%20sheet.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Excel%20links.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219110i0B5C65B639B7DB51%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Excel%20links.png%22%20alt%3D%22Excel%20links.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20idea%20is%20to%20compare%20the%20current%20date%20with%20the%20ones%20in%20the%20excel%20sheet%20and%20if%20there%20is%20a%20scheduled%20meeting%20today%20the%20shortURL%20should%20be%20changed%20to%20the%20corresponding%20URL%20stated%20in%20the%20excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20changes%20for%20the%20shortURL%20works%20via%20a%20REST%20API%20perfectly.%20But%20unfortunately%20I'm%20not%20able%20to%20figure%20out%20the%20Excel%20part.%3C%2FP%3E%3CP%3EI'm%20able%20to%20list%20the%20rows%20present%20in%20a%20table%20and%20also%20to%20convert%20them%20into%20date%2Ftime%20format%2C%20but%20I'm%20not%20able%20to%20compare%20them%20with%20the%20actual%20date.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20Power%20Automate.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219111iE07FFAA50B43FBE9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Screenshot%20Power%20Automate.png%22%20alt%3D%22Screenshot%20Power%20Automate.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20guess%20it's%20pretty%20easy%20to%20accomplish%20for%20a%20pro.%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1677651%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMicrosoft%20Flow%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20Automate%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1687208%22%20slang%3D%22en-US%22%3ERe%3A%20Compare%20excel%20data%20with%20actual%20date%20in%20Power%20Automate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1687208%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F642475%22%20target%3D%22_blank%22%3E%40Thomsch%3C%2FA%3E%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAdd%20an%20OData%20filter%20to%20the%20Excel%20%22List%20rows%20present%20in%20a%20table%22%20action%20to%20find%20events%20that%20are%20occurring%20today.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20the%20expression%3A%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3Ediv(sub(ticks(utcNow('yyyy-MM-dd'))%2Cticks('1899-12-30T00%3A00%3A00'))%2C864000000000)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22stormin_30_0-1600513730787.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219826i2CE8F384D5AC8AA3%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22stormin_30_0-1600513730787.png%22%20alt%3D%22stormin_30_0-1600513730787.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EImportant%3A%20there%20are%20single%20quotes%20('%20')%20around%20the%20expression.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20hope%20this%20helps.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENorm%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Frequent Contributor

I have a list of all upcoming Teams live events with date and time within an excel sheet.

Excel links.png

 

The idea is to compare the current date with the ones in the excel sheet and if there is a scheduled meeting today the shortURL should be changed to the corresponding URL stated in the excel.

 

The changes for the shortURL works via a REST API perfectly. But unfortunately I'm not able to figure out the Excel part.

I'm able to list the rows present in a table and also to convert them into date/time format, but I'm not able to compare them with the actual date.

Screenshot Power Automate.png


I guess it's pretty easy to accomplish for a pro.

Thanks in advance.

 

 

 

 

5 Replies
Highlighted

Hi @Thomsch,

 

Add an OData filter to the Excel "List rows present in a table" action to find events that are occurring today.

 

Here is the expression: 

div(sub(ticks(utcNow('yyyy-MM-dd')),ticks('1899-12-30T00:00:00')),864000000000)

 

stormin_30_0-1600513730787.png

Important: there are single quotes (' ') around the expression.

 

I hope this helps.

 

Norm

 

Highlighted

@stormin_30 thanks. This helps me out a lot!

 

I actually have two appointments each Tuesday. And the meeting link is different for both of them.

How would you proceed to check which one of the meetings is the right one for the link to change.

 

Highlighted

Hi @Thomsch,

 

You will need something to further filter or connect the meeting to the link. Perhaps a unique meeting name? I don't think ID, Date and URL are enough.

 

I hope this helps.

 

Norm

 

 

Highlighted

@stormin_30 Would it be possible to look for the next/nearest meeting regarding time? 

Highlighted

Hi @Thomsch,

 

I'm sure that's possible. In the "List rows in a table" action add:

  • "Date" to "Order By"
  • "1" to "Top Count"

I have not tested this but it seems reasonable to me.

 

I hope this helps.

 

Norm