Unanswered

"Filter cells in Excel worksheet" action changes string to numbers.

(0) Share

Report

Posted on by awaples

I am using the filter cells action in PAD, and I have a table in an excel file with a column containing the dates of requests sent to my team. I need to filter for a month, and when I do so in excel, if I hover over the filter it says for example:

Equals "January 2024"

But when I have a variable equaling "January 2024" in PAD and try to use it as a filter as such:

The excel table filter ends up saying:

Equals "45292"

What am I doing wrong here, and why is it turning my string into some numbers in the filter?

Categories:

Power Automate Desktop

I have the same question (0)

All responses (3)

Answers (0)

Sort by

MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Excel recognizes dates as a five digit number. Is the variable you are using a date type variable or a text type variable.

if it is a date type variable, us “convert datetime to text” to make it a text variable and try again with that one.

Was this reply helpful? Yes No
awaples 39 on at

Like
a
(0)

Report
Copy link

Link copied!

It is a text variable. I've found that by using the simple editor for the filter menu, I can use the filter "(Eq %monthfilter%)", which upon running the flow seems to properly give text to the table filter in excel (see below)
The thing is, it still doesn't work! If I click the filter menu, it shows it as completely empty!
If I then select January, it works perfectly fine but hovering still gives the same result
I feel like I'm losing my mind here.

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

So the date pickers on this filter are difficult. Do you have any leniency on messing with the worksheet itself?

Example:

You could add a new column to the right, have a cell above that represents the year/month you want to search for, write the year/month into row 1, such as 01/01/2024. In the rows below, have a formula that says if the Month()_Year() of the Submission time = the Month()_Year() of the input cell, true, false. Then you could filter that column for True.

Was this reply helpful? Yes No