“The reminder flow should send an email 2 days before a date stored in an Excel file, but Power Automate sends email for each row, the condition doesn’t work.”
Every time you process only a selection of the data you have, you should use a filter. You don’t want to take all the data, loop through it, and use a condition that would process only some of them. It’s much better to filter only the relevant data right away. Your flows will be simpler, faster, and easier to live with.
The same applies also to dates in an Excel file, even though they’re provided as a sequence number. You might be inclined to take all the rows, convert the number into a date, and then use a condition. But that’s not the right approach. If you can filter directly in the ‘List rows present in a table’, you should do it. Even if you must use the sequence number.
Get today’s date sequence number
As in the other situations when filtering by a date, you must get today’s date. Working with dates from Excel file it means today’s sequence number. The conversion process is a partial reverse of the conversion from sequence number to date, as described e.g. in the Planner tasks importing post.
Get ticks until today
Take the ticks(…) value of today’s date to get the number of ticks for today. One tick is 100 nanoseconds, and the calculation starts from 1st of January 0001 00:00:00. The result will be a number of ticks from 0001-01-01 until today.
ticks(utcNow())But you’re not interesting in the time period since the 0001-01-01. You’re interested in the time period since 1899-12-30 as that’s the base date for the Excel date sequence.
Therefore, you’ll need the number of ticks(…) until the Excel base date of 30th of December 1899. The result will be a number of ticks from 0001-01-01 until 1899-12-30.
ticks(formatDateTime('1899-12-30'))Note: the date must be converted from a string to a date with the formatDateTime(…) for the ticks(…) expression.
Now, if you subtract the Excel base date ticks from the today’s ticks, you’ll get the time period from the Excel base date until today.
sub(ticks(utcNow()),ticks(formatDateTime('1899-12-30')))
0001-01-01 until today - 0001-01-01 until 1899-12-30 = 1899-12-30 until todayConvert the ticks into days
The result is today’s number of ticks from the Excel base date. But as mentioned earlier, one tick is 100 nanoseconds. To convert it into days you must divide it with the div(…) expression by 864000000000.
Note: 24 hours * 60 minutes * 60 seconds * 1000 miliseconds * 1000 microseconds * 10 nanoseconds = 864000000000.
div(sub(ticks(utcNow()),ticks(formatDateTime('1899-12-30'))),864000000000)This expression will give you the sequence number for today, in the same format as used by the dates in an Excel file. Add / subtract a few days for the reminder, and use the expression in the Filter Query. For example, to get rows with the date 2 days in the future (todays sequence number + 2).
add(div(sub(ticks(utcNow()),ticks(formatDateTime('1899-12-30'))),864000000000),2)You’ll filter only the relevant rows and process them right away, without any additional conditions.
Summary
Reminder flow in Power Automate that’s based on date in an Excel file is a bit more complicated than if you used a SharePoint list. It’s because Excel won’t give you date as a date, but as a sequence number. From there you’ve got two options. The first one is to get all the rows, convert the number into a date, and then compare it in a condition. That’s the option I wouldn’t recommend.
The second approach, described in this post, will take it the other way around. If you can convert a sequence number into a date, you can convert also a date into a sequence number. And if you base the sequence number on the same base date as Excel does, you can get the corresponding value and use it in the Filter Query.
A simple flow with just a few actions, that can be extended e.g. by sending just one reminder per user.