How to get the excel data from -7 days to -3 years from .xls file

Hi, I am trying to extract the data from -7 days to -3 years range and I tried with the filter data table but unfortunately not getting the result .
test_duedate.xls (537 KB)

Hi @Syed_Sultan_Ahmed1

Can you specify for which column you need to filter the range.


I need to filter due date column

i tried with the Filter DT


ExcelDt=ExcelDt.AsEnumerable.where(Function(x) cdate(x(“Due date””).ToString)>CDate(now.AddYear(-3).ToString(“dd-MMM-yyyy”)) And cdate(x(“Due date”).ToString)<CDate(now.AddDays(-7).ToString(“dd-MMM-yyyy”))).CopyToDataTable

try this hope this helps


What activity do you use to read it to datatable?
If you use ReadRangeWorkbook, it returns datetime as OADate value.
So can you try UseExcelFile activity and ReadRangeX activity (or Classic ReadRange if ReadRangeX is very slow), then use Filter DataTable with the above condition?


Hi , I am getting the error with this expression “ADDYear is not an member of Date”

Hi @Syed_Sultan_Ahmed1 ,

Could you maybe check with the below Steps :

  1. Read the Excel sheet as a Datatable using Read Range Workbook activity, Keep the PreserveFormat property enabled.
  2. Next, For the Filtration we could use the below Linq Expression :
drArray = DT.AsEnumerable.Where(Function(x)CDate(x("Due date").ToString)<=Now.AddDays(-7) andAlso CDate(x("Due date").ToString)>=Now.AddYears(-3)).ToArray

Here, drArray is a variable of Type Array of DataRow.

  1. Next, we can check if there are any Filtered Rows present and then convert to Datatable, else We can assign Clone of the Datatable.

Implementation :

Findings when read as PreserveFormat, the dates are in MM/dd/yyyy format, so the CDate() was used directly :

What is the datatype of drArray,

@Syed_Sultan_Ahmed1 ,

It is mentioned.


getting this error

tried that but its not working

@Syed_Sultan_Ahmed1 ,

Was the same input file checked or a Different file ? Also, Let us know what activities you have used or the Implementation done so far.

Please check if I am doing something wrong (120.7 KB)

@Syed_Sultan_Ahmed1 ,

I did check your workflow, It seems that you are using Excel Application Scope, with Excel application Scope it seems that you are able to get the date values in the required format, Hence No need to Enable PreserveFormat Property.

Could you disable PreserveFormat property and check whether it is able to get the desired results ?

Thank you its working fine and getting the expected result…

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