Comparing Dates from Excel to send emails

[Halfspace]

Hiya Geniuses

I'm still working on improving my leavers flow and want to send an approval task to a few areas of the organisation when someone leaves.

I'm setting the leaver date in a one Drive Excel sheet (formatdatetime(utcnow(),'yyyy-MM-dd')).

I'm then setting up a recurring time check to see if that date matches todays date, in which can do the trigger to send the approval tasks   That is where I'm failing.   I'm pretty sure it's because I'm doing something dumb with Excel formats.   I've checked the forums and have tried these that seemed to work for other people (but not me!):

1) Using list Rows present in a table, filtering by todays date (filter query = LeftDate eq formatdatetime(utcnow(),'yyyy-MM-dd') but I get an error that only only eq, ne etc is allowed.

2) Adding in a filter array that compares LeftDate equals formatdatetime(utcnow(),'yyyy-MM-dd')  - that said the dates had to be in ISO8601 format

3) Updated the List Rows to have date format of ISO8601 - still fails.

4) In the filter array iupdated the LeftDate format to be the same format (formatDateTime(item()?['DateLeft'], 'yyyy-MM-dd') is equal to formatdatetime(utcnow(),'yyyy-MM-dd') - still fails with non ISO8601 format.

Im so sure theres just a simple comparison to the original excel filter that I'm just missing.   Does anyone have any bright ideas?

Thanks so much in advance.

Mark

[Nived_Nambiar]

Hi @Halfspace 

Have you entered the filter query like below in list rows in excel action

LeftDate eq 'formatdatetime(utcnow(),'yyyy-MM-dd')'

[Halfspace]

Hi @Anonymous 

Thanks for trying to help, I used the expression creator to do that rather than entered it direct, but it gave me the "no more than one eq, ne" error.   If I enter it directly into the filter query line copying from your comment I get a syntax error:

Sorry if Im doing something dumb!

[Nived_Nambiar]

Hi @Halfspace 

Do one thing

Run the list rows action without specifying any query 

Then use compose function to check values dynamic variable from List rows action. 

Check how the date is present in that json 

Let me know

[Halfspace]

Im not familiar with the compose action, but assuming I just add Compose, and then DateLeft in input, it gives me this.  

If I pull from the raw output of the list on the run history and then find my test row (with todays date in it) it also gives me that - so guess that all matches. 

Thats why I tried to then format that in the filter row so it matched (my point 4).

I hate dates in Excel 🙂

[Nived_Nambiar · Accepted solution]

I was right 🙂

That number actually represent the days the given date in excel is from date of 1 January 1900 , for current date it is 45007 days from 1 jan 1900  [include both start and end dates as well]

So we may have to compare by this filter query

Date eq '

add(div(sub(ticks(formatDateTime(utcnow(),'yyyy-MM-dd')),ticks('1900-01-01')),864000000000),2)'

This highlighted one has to be put in expressions 

Mark it as solution if this works for you 🙂

[Halfspace]

Good grief - I have no clue what that does but it works :).  Thanks very very much!   Can categorically say I wouldn't have worked that out...

[Nived_Nambiar]

I did not understand what you said at last, could u explain it better ?

[Halfspace]

I was just saying that your solution was way more advanced than I could have created myself, so I really needed the help.   Hope thats clearer 🙂

[Nived_Nambiar]

Hi @Halfspace 

Its very simple

so number which was appearing in the excel when reading via power automate was the days from Jan 1st 1900 to the date given in excel [Both dates included]

ticks is a function which returns the number of ticks (1 tick = 100 nano seconds ) starting from January 0001 to the date given as input. 

In this code we first convert the dates (Current date & Jan 1st 1900) to ticks using ticks function and then subtract them , so that is why we have used sub() function. After subtracting the ticks, the resultant value is divided by  864000000000  [ 1 day = 864000000000 ticks] for dividing we have used div function once we divided we will get the duration b/w dates in terms of days. 

Finally we need to add 2 so we can consider both end dates (here utcnow() and 1900 Jan 1st) so we have used add function at last.

For getting more information about tick function refer the following link

https://manueltgomes.com/reference/powerautomate-function-reference/power-automate-ticks-function/

Hope this helps 🙂