Filter only current month rows in given data table

Filter only current month rows [column name :Expiration Date] in given data table

need any select or Linq to filter Expiration Date column to find out this month data.


Can you try the following expression?

dt = dt.AsEnumerable.Where(Function(r) DateTime.Parse(r("Expiration Date").ToString).Month = Today.Month AndAlso DateTime.Parse(r("Expiration Date").ToString).Year = Today.Year ).CopyToDataTable


Hi @prabhu_ponnusamy

Can you try to use the Filter Data table (Refer to the screenshot)


no luck. returned unexpected values

String was not recognized … exception does mean that expected format / data is different

give a first try on trimming the date values by

DateTime.Parse(r(“Expiration Date”).ToString.trim()).Month
do it also for the Year part of Yoichi’s approach

in case of it will fail again we can check more in detail the failing dates within a second anlysis round

@prabhu_ponnusamy - Is it possible to share sample data after masking/removing sensitive information?

Hi @prabhu_ponnusamy ,

If ppr’s solution still doesn’t work, there might be blank cell and/or illegal characters as datetime string. The following will filter out those.

dt = dt.AsEnumerable.Where(Function(r) DateTime.TryParse(r("Expiration Date").ToString.Trim,New DateTime()) AndAlso  DateTime.Parse(r("Expiration Date").ToString.Trim).Month = Today.Month AndAlso DateTime.Parse(r("Expiration Date").ToString.Trim).Year = Today.Year ).CopyToDataTable

Note: Perhaps you should check if this doesn’t filter out rows which you want to keep.


1 Like

Test.xlsx (25.9 KB)

Attached is the correct excel where we do apply this rule

Expiration Date has rows with empty values along with a space. This is the reason for the invalid format expception

It is now to define on how to handle this

  • use a default date
  • do cleansing on a copy of the datatable and do afterwards the filtering (give a check on this)

Are you sure??

I did not find any rows for the current year…


Please look into it…

if its just for the current month for any year…here is the filtered output…please verify…