Filtering by date range with Excel connector using Power Automate
So it’s another awesome day, you had a great breakfast, had a nice drive with a view to work and your boss has just given one simple task for the day that is “Make a flow that generates a report every week, of items whose delivery date is within next 30 days”.
Pretty simple right? Of course, it is. But when you started using excel connector in power automate, it gives you dates like THIS?!
But where did you go wrong? You didn’t. You did all the right things. It’s not you. It’s not me either. It’s the connector.
But what are those numbers anyway? So, those numbers are the number of days that have passed from the 1st of January 1900 (by default). Why so? You can read about the date systems in excel here: https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b487
Now that we know what are those numbers, everything becomes easier. We’ll learn how we can tackle this problem.
Before we start, let’s look at the sample data which we need to filter with Power Automate.
Now that we have understood our problem statement, have an idea of how to solve it, and have the sample data ready, let’s start solving the problem starting with setting up our basic flow which holds the start and the end date.
Let’s add 2 date time connectors, one with “Current Time” action and another with “Add to Time” action. For “Add to Time”, we add 30 days to the time obtained from the current time. Your flow should look something like this now:
Now we need to get the date-time obtained from these actions into a similar numerical format as returned by the excel connector. But how do we do that? We have the formula to compute that as mentioned below:
(Ticks representation for Date to be converted — Ticks representation for 1899–12–31) / 864000000000
Now that we know the magic formula, let’s apply it in our flow. Add two actions to initialize variables and add the below expression in the values:
- First variable for the current time:
div(sub(ticks(body(‘Current_time’)),ticks(‘1899–12–30’)),864000000000)
- Second variable for the future time:
div(sub(ticks(body(‘Add_to_time’)),ticks(‘1899–12–30’)),864000000000)
Your flow should now look something like this:
We now have all the required parameters in appropriate formats. We are just left with getting the data and applying the filter upon it. Let’s get on with it.
Add the “List Rows Present in a Table” action for the “Excel Online (Business)” connector and select the correct data file and table name as shown below:
For the final step, we are going to add the “Filter Array” action and put an appropriate formula to get the final results. We are going to add an expression to mimic the following formula:
Delivery Date > Current Time AND Delivery Date < Future Time (30 days from Current Time)
Since our formula has an AND condition, we cannot use the basic mode of Filter Array action. Click on “Edit in Advanced Mode” hyperlink and add the following expression
@and(greater(int(item()?[‘DeliveryDate’]),variables(‘current time ticks’)),less(int(item()?[‘DeliveryDate’]),variables(‘future time ticks’)))
And…we are done! Our flow is now ready to filter a date range from excel and output the appropriate results. Your final flow should look like this.
It’s time to see our flow in action. Before you run the flow, adjust the dates in your excel sheet for multiple test cases with some dates which have passed, some which are in the next 30 days, and some which are beyond 30 days. Let’s test it!
I ran this test on the 25th of September 2021 on the below data and the highlighted rows should be returned after applying the filter:
And as expected after running the flow, the filter array action gave the following output:
Why can’t we use an ODATA filter query instead?
If you had this question as well then good job! You have a good understanding of Power Automate. If you did not, you should learn about ODATA filter queries as it will make your life much easier in a lot of ways and not only in Power Automate but in writing custom APIs, developing PowerApps etc as well.
You can have a quick and detailed introduction to ODATA filter queries in this awesome blog post: https://diyd365.com/2019/11/20/every-power-automate-ms-flow-filter-query-you-ever-wanted-to-know-as-a-functional-consultant/
Now, the answer to the above question is that we can use it with other connectors like Sharepoint but not with the Excel Connector.
If you try to use the ODATA filter query as shown below, you will face an error as the Excel connector doesn’t support all ODATA operators yet
Thank you for taking out time to read this article and I hope it helps you in your automation journey with Power Automate. If you have any questions, please feel free to reach out to me or drop them in the comments.
Stay tuned for more such content on Power Platform.
Keep Learning. Keep Growing.
