Apply date filter on excel sheet

Help
,
1

I have a sheet that contains leaves applied by employees.
This sheet has leaves applied for atleast last 4 months. (“mm/dd/yyyy”)
Ex: 1 employee has applied leave like from:1/15/2018 to:1/17/2018
And same employee has also applied from:2/4/2018 to: 2/4/2018
Now there’s another who has applied leaves as from:1/1/2018 to:4/31/2018

I want to apply filter or extract leaves applied only for certain month in all three examples.

I have wracked my brain. tried applying month filter…but got the error- January is not a column/feb is not a column…

Any help is greatly appreciated.

1 Like
2

Hi @Anu14,

Use read range activity to get the data from the excel sheet (Dt)

DateTime firstDayOfMonth = new DateTime(date.Year, date.Month, 1)

DateTime lastDayOfMonth = new DateTime(date.Year, date.Month, DateTime.DaysInMonth(date.Year, date.Month))

Use assign activity
Dt=Dt.Select("[Leave Start Date] <= '" + firstDayOfMonth+ "' AND [Leave Start Date]>= '" + lastDayOfMonth+ '"'). CopyToDataTable()

Regards,
Arivu

3

Hi Arivu,
Thanks for your reply. I tried what you mentioned but got 0 rows in return when infact there are employees who have applied leave in that month.

4

Hi @Anu14,

Great, its for current month only it will return.

Regards,
Arivu

5

Yes so i tweaked it a bit to use last month- feb’s data but got 0 rows in return.
Even though the syntax is correct. I dont think excel is filtered as desired.

6

Hi, could you show your tweak to use last month?

Here is an idea to only check “last” month (going off of Arivu’s solution):

DateTime firstDayOfMonth = new DateTime(date.Year, date.AddMonths(-1).Month, 1)

DateTime lastDayOfMonth = new DateTime(date.Year, date.AddMonths(-1).Month, DateTime.DaysInMonth(date.Year, date.Month))

Dt=Dt.Select("[Leave Start Date] <= '" + firstDayOfMonth+ "' AND [Leave Start Date]>= '" + lastDayOfMonth+ '"'). CopyToDataTable()

I didn’t really change anything but added in .AddMonths(-1)

Alternatively you can do this with .Where (which is more versatile to me):

Dt = Dt.AsEnumerable.Where(Function(r) CDate(r(0).ToString.Trim).Month  = Now.AddMonths(-1).Month ).CopyToDataTable

Regards.