Unanswered

Comparing Dates from Excel to send emails

(0) Share

Report

Posted on by 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

Categories:

Building flows

I have the same question (0)

All responses (9)

Answers (1)

Nived_Nambiar 18,129 Super User 2025 Season 2 on at

Like (0)

Report

Hi @Halfspace

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

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

Was this reply helpful? Yes No
Halfspace 19 on at

Like (0)

Report

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!

Was this reply helpful? Yes No
Nived_Nambiar 18,129 Super User 2025 Season 2 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Halfspace 19 on at

Like (0)

Report

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 🙂

Was this reply helpful? Yes No
Verified answer

Nived_Nambiar 18,129 Super User 2025 Season 2 on at

Like (0)

Report

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 🙂

Was this reply helpful? Yes No
Halfspace 19 on at

Like (0)

Report

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...

Was this reply helpful? Yes No
Nived_Nambiar 18,129 Super User 2025 Season 2 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Halfspace 19 on at

Like (0)

Report

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 🙂

Was this reply helpful? Yes No
Nived_Nambiar 18,129 Super User 2025 Season 2 on at

Like (0)

Report

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 🙂

Was this reply helpful? Yes No