Filter Excel sheet based on date. Issue with Date format

Help
, , ,
1

I’m having an issue with using Filter Data Table to only show rows in an Excel file with a date of Today or before today (based on current system time). I think it might be because my sheets column is not formatted in a way that lets the filter work.

This is a sample of the sheet. I want to filter the Expected Receive Date to anything that is today or before today.

excel1
excel11430×105 8.28 KB

It works if I only use my Account variable, but returns nothing (not even headers) if I try to filter by date.

excel2
excel21190×667 31.8 KB

Do I need to rebuild the table with a For Each Row activity? I’m a little stuck.

2

Bumping this. Anyone with any help is greatly appreciated!

3

@Freeman_Alex
find some starter help here:
DT_FilterDates_ByDaysSegment.xaml (8.4 KB)
DT_FilterDates_Yesterday.xaml (8.3 KB)

4

Thank you for the guidance! Maybe I can provide some more detail to explain my issue better.

Here is a test sample of the Excel file I need to filter.

Data.xlsx (9.8 KB)

Here is a copy of what I have now. It successfully takes the account number entered and filters the data.

FilterandSave.xaml (16.2 KB)

I need it to also filter the Expected Receive Date to anything that is today or before today and then save the file. The problem I have is that if I put any filter on the Expected Receive Date column, I get no data returned.

Any ideas on a way to fix that?

5

My sheet in the Excel example was not named Master. This has the correct sheet name.

Data.xlsx (9.8 KB)

6

@Freeman_Alex
find starter help for your case here:
FilterandSaveV2.xaml (9.8 KB)

Correct your Excel data, as the Dates are invalid and would throw an error
grafik

Ensure Following:
grafik

Kindly note:
the date values displayed in Excel are not mandatory the values within the datatable after read range. In case of not valid date string issues, debug the xaml and check the different formats.
The configuration of the formats can be done here on the Variable Formats:

grafik
grafik826×36 8.14 KB

1 Like
7

Thanks again for your help! However, I don’t think this is what I was looking for. I am looking to first find an account number, then filter it down to only rows that contain an Expected Receive Date of the current system date and before, then save the new file as an Excel. The Excel I receive with the data in it will always be in the improper date format (it can’t be modified by the system that exports it), so I would either need the robot to rebuild the date format correctly or find if there is an alternate way to filter down the date as it is already formatted in the Excel.

I do appreciate the guidance. This appears to be a more complex problem than I had initially anticipated.

8

@Freeman_Alex
currently the demo is only focusing on the filter part and should help you on this.
Feel free modify the filter condition

Regardless what the process is about as december (12) is the last month in the year we do not have more months. So 2020-28-14 is an invalid date and will throw an exception with any date format.

Getting handled invalid datestring we can filter out such invalid dates. The code for this you can find here in the forum . It just depends on the requirements on what to do with.

9

Thank you again for your response. The demo is causing an error and not filtering the way I need it to, though.

error1
error11162×621 55.7 KB

RemoteException wrapping System.FormatException: String was not recognized as a valid DateTime.
at System.DateTimeParse.ParseExactMultiple(String s, String formats, DateTimeFormatInfo dtfi, DateTimeStyles style)
at System.DateTime.ParseExact(String s, String formats, IFormatProvider provider, DateTimeStyles style)
at lambda_method(Closure , DataRow )
at System.Linq.Enumerable.WhereSelectEnumerableIterator2.MoveNext() at System.Linq.Enumerable.WhereEnumerableIterator1.MoveNext()
at System.Linq.Enumerable.WhereSelectEnumerableIterator2.MoveNext() at System.Collections.Generic.List1…ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)
at lambda_method(Closure , ActivityContext )
at Microsoft.VisualBasic.Activities.VisualBasicValue1.Execute(CodeActivityContext context) at System.Activities.CodeActivity1.InternalExecuteInResolutionContext(CodeActivityContext context)
at System.Activities.Runtime.ActivityExecutor.ExecuteInResolutionContext[T](ActivityInstance parentInstance, Activity1 expressionActivity) at System.Activities.InArgument1.TryPopulateValue(LocationEnvironment targetEnvironment, ActivityInstance activityInstance, ActivityExecutor executor)
at System.Activities.RuntimeArgument.TryPopulateValue(LocationEnvironment targetEnvironment, ActivityInstance targetActivityInstance, ActivityExecutor executor, Object argumentValueOverride, Location resultLocation, Boolean skipFastPath)
at System.Activities.ActivityInstance.InternalTryPopulateArgumentValueOrScheduleExpression(RuntimeArgument argument, Int32 nextArgumentIndex, ActivityExecutor executor, IDictionary2 argumentValueOverrides, Location resultLocation, Boolean isDynamicUpdate) at System.Activities.ActivityInstance.ResolveArguments(ActivityExecutor executor, IDictionary2 argumentValueOverrides, Location resultLocation, Int32 startIndex)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

I appreciate your help, but I’ll have to take this to the project team I work with to investigate further. Perhaps it’s easier to find some other way to generate the data I need.

Thanks again!

10

exactly what i was talking about. so handle / filter out invalid dates or do some other issue handlings

1 Like
11

Ah, I see where I misunderstood you! I supplied invalid dates in my Excel. We will always get the Excel with valid dates as it is exported from another system. I wanted to provide sample data here and must have mistyped a date!

Now that I fixed the mistake I made I think I have a big success! I ran a few tests and it filtered perfectly! Thank you so much for your help, you’ve saved me so much!

12

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.