I need to filter one year and one month before date in excel sheet from current date. I tried so many ways, but it can,t work. If anyone knows how to do that, please share with me

Help Studio
, ,
1

I need to filter list of one year and one month before date in excel sheet from current date and save in same excel file in different sheet.

(For Example, today’s date is 15-12-2021…I have to filter from 15-11-2020 to 15-12-2021) in Excel sheet.

I tried so many ways, but it can,t work. If anyone knows how to do that, please share with me.

2

Hi @lokeshkumar.k Do you have any sample input?

3

Also You can checkout this thread @lokeshkumar.k

Regards
Sudharsan

4

HI @lokeshkumar.k

You can Try with LINQ expression

DtOutput → System.data.DataTable

DtOutput = DtreadFull.AsEnumerable.Where(Function(x) Not(String.IsNullOrWhiteSpace(x("Column Name").ToString)) AndAlso (CDate(x("Column Name").ToString)<(new DateTime(Now.Year,Now.Month-12,1)))).ToArray.copytodatatable

Regards
Gokul

5

yes…I have sample input file

6

okay can you send that here?

7

Sample File.xlsx (13.3 KB)

8

I have to filter from this excel sheet from current date.

9

DtreatFull means ??

10

@lokeshkumar.k

Read Range Activity Output

11

Can you share the Output for this Input file @lokeshkumar.k

12

I have to filter the list of one year one month back dates from current date from this excel file.

13

There is two column name mentioned in the query. but i try to filter the date compared to current date only

14

please suggest any query for this one.

15

image
image1920×1080 139 KB

16

@lokeshkumar.k
Try this

DateTime.Now.AddMonths(-13)<ExcelDate and DateTime.Now>ExcelDate

ExcelDate is in Date format of your excel date data

17

and I need to filter one year back also

18

@lokeshkumar.k
This Conditions allow only past 13 months data and current month data

DateTime.Now.AddMonths(-13)<=ExcelDate and DateTime.Now>ExcelDate

This Conditions allow only past 12 months data and current month data

DateTime.Now.AddMonths(-12)<=ExcelDate and DateTime.Now>ExcelDate
19

Without give column name. how its working ?

20

You have to pass column name in “ExcelDate” variable as a date format