How to filter date format dd/MM/yyyy in excel for a previous month

[iwie]

Hi guys,

I am trying to filter my data from Excel. In one column I have date in format dd/MM/yyyy and would like to filter it for the previous month. For that I use variable:
myFilterVar = DateTime.Now.AddMonths(-1).toString(“MM/yyyy”)
filter wizard = “ColumnName” Contains {“*myFilterVar”}

With this I am not getting any result, however my DT is not blank. Any thoughts?

[HareeshMR (Hareesh Madasi)]

You can use filter data table activity to do that. You will achieve it easily using that.

[iwie]

Yes, that’s what I am doing, using Filter Data table with Filter wizard.
my filter wizard is as:
“ColumnName” Contains {“*myFilterVar”}

[Lahiru.Fernando]

Hi @iwie

Can you share your workflow along with sample data excel so we can have a look?

[iwie]

Hello @Lahiru.Fernando

find below a sample xaml file as well as the xlsx.file.
Sample Filter date.xlsx (32.2 KB)
Sample Filter date.xaml (18.2 KB)

The message box brings my only headers without any data. Thanks for your help!

[Lahiru.Fernando]

Hi @iwie

I did some changes to your xaml file to incorporate the change you need. This works exactly as you require…

I have added comments into the workflow activities that I added so you can understand easily why I have done it and what happens there

Sample Filter date.xaml (24.2 KB)

Hope it helps!!

If this works out for you, please mark my answer as the solution too…

Let know how it goes

[iwie]

Hi @Lahiru.Fernando
it works great when I run it from the xaml, which we shared. If I move it to the file, which I have to work with, it gives me error. Are there any variables I have to create additionally? What can be the problem?

[Lahiru.Fernando]

Hi @iwie

Can you try running this in the debug mode so that we can locate exactly which assign activity is causing the problem?

Right click on the for each loop, add a break point and run in the debug mode. Once it gets there, try running it in step by step so we can exactly see what is going on …

[iwie]

Hi @Lahiru.Fernando

seems like the Assign to DateTimeTo activity is causing problem, while the variable type is a DateTime.

[Lahiru.Fernando]

HI @iwie

Did you change anything in that code? It works fine for the excel you shared with me and it takes the data without any issue in mine…

Is it possible to re-share you solution so I can have a look why it is not working there?

[iwie]

Hi @Lahiru.Fernando,

so in the original file, not each row has a real date in column “To”. Some of them have “N/A” , because it’s not applicable. How can I solve this?

[Lahiru.Fernando]

yes… working on it… give me few minutes…

[Lahiru.Fernando]

Hi @iwie

Check the attached file. I have added an extra level of validation when converting the value to DateTime format. This should resolve the error…

Sample Filter date.xaml (25.2 KB)

Let know whether this works…

[iwie]

@Lahiru.Fernando, it’s perfect! Thank you I really appreciate your help.