Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Triggers
  • Resources
  • ABOUT ME
  • Get help with your flow
Menu

How to send a reminder on a date stored in Excel file with Power Automate

Posted on July 11, 2021July 11, 2021 by Tom

“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 today

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

Power Automate reminder Excel date

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.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Do you know what to do, but not how to do it?

Get The Ultimate Power Automate expressions cheat sheet and translate your thoughts into flows with ease!


There's also the HTTP requests to SharePoint cheat sheet to help you overcome limitations of some standard actions!

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

  • How to find Excel rows missing in SharePoint list (Power Automate)March 29, 2023
  • Check whether user exists in AAD with Power AutomateMarch 26, 2023
  • How to highlight whole SharePoint row based on calculated dateMarch 22, 2023
  • Why is your Power Automate flow running so slow?March 19, 2023
  • How to add multiple links to the Power Automate approval taskMarch 15, 2023

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2023 Let's POWER Automate | Theme by SuperbThemes