Filter Date In Excel Column

Learn Academy Feedback
1

Hi,
Im currently working on some excel file that need to filter date in “Reg Date” column according on business working days. Lets say if today is Monday, need to filter the date 3 days before, as such if it is weekdays the date filter should filter date 1 days before. Can anyone help me out in this. Im new on Uipath so any help will be so much appreciated. Try to attached the excel file but seems i cannt cause im new it says. so I attached the image as your references. Thank you!

image
image1002×751 28.7 KB

1 Like
2

Typically when you read and Excel spreadsheet using Read Range, the datetimes get formatted as datetimes in the workbook. After you’ve used Read Range to get the data, you can check if each date is a weekday in a For Each Row loop with an If condition set to System.Date.Weekday(row("Reg Date") >= 2 AndAlso System.Date.Weekday(row("Reg Date") <= 6, where row is the row from the For Each Row loop.

3

Hi i was trying to put the System.Date.Weekday(row(“Reg Date”) >= 2 in the if activities but i got an error of i was missing something like () at the end of expression. May i know am i doing it right? i put the if else inside the for each row activities right after read range activities.

4

My mistake, it should be System.Date.Weekday(row("Reg Date")) >= 2 AndAlso System.Date.Weekday(row("Reg Date")) <= 6

5

Hi Anthony,

I do try your suggestion. But somehow im doing it wrong perhaps. Are this the correct way of how im doing it?

image
image1039×859 28.2 KB

6

Change it to Weekday(row("Reg Date")) >= 2 AndAlso Weekday(row("Reg Date")) <= 6.

7

You could also consider the filter datatable activity. I find it easier.

8

Hi kamalen,

i have tried that before. but somehow its not working. the only data that it fetch is just a string of “Data table” when i was testing it out using the output data table activity to see what output that it gives. Perhaps you have a better way to show me?

9

Still fail where the error says implicit conversion from object to date.

10

Then use Weekday(Datetime.ParseExact(row("Reg Date").ToString, "M/d/yyyy h:mm:ss tt", System.CultureInfo.InvariantCulture)) >= 2 AndAlso Weekday(Datetime.ParseExact(row("Reg Date").ToString, "M/d/yyyy h:mm:ss tt", System.CultureInfo.InvariantCulture)) <= 6.

11

Have you tried assigning the strings that you would use as the value in the filter datatable activity wizard before the activity?

So, something like:

  1. Create the strings that are the date that you would consider to be within your limits.
  2. Using the contains operation in the Filter Wizard.
  3. Making the Value in the wizard the dates that you created in 1.
1 Like