How to extract previous Month date value from Excel

Hi All,

Please help me extract the only previous month data from the attached excel in Month column tried many ways but not able to get the result.

Test.xlsx (11.2 KB)

1 Like

Hi @manjusree_R_N_manju ,

Could you maybe try using the below Expression in an Assign activity instead of Filter Datatable and check if it works :

DT1  = DT.AsEnumerable.Where(Function(x)Not String.IsNullOrWhitespace(x("Month").ToString) andAlso CDate(x("Month").ToString).Month = Now.Month).CopyToDatatable

Here, Month is assumed to be the column name in Excel sheet that is to be filtered.

Direct use of CopyToDatatable could give out Exception if there are no rows found after Filtering. Hence, to handle this case, we could implement the below method :

If there are any other errors shown, Do provide more info on that error, if possible with screenshots so we could help you further.

Hi, Thanks for the reply Tried the above solution it’s filtering Feb data current month data i want previous month data

Hey @manjusree_R_N_manju
here you can find working workflow: (11.5 KB)


you can use this expression in assign activity.

dt.AsEnumerable.where(Function(a) CDate(a(1).ToString)>=new DateTime(now.Year,now.Month,1).AddMonths(-1) And CDate(a(1).ToString)<=new DateTime(now.Year,now.Month,1).AddDays(-1)).CopyToDataTable

can you please provide more information about what to do if there are values in 117 and 116 rows

@manjusree_R_N_manju ,

Apologies. A Slight modification of the above Expression :

DT1  = DT.AsEnumerable.Where(Function(x)Not String.IsNullOrWhitespace(x("Month").ToString) andAlso CDate(x("Month").ToString).Month = Now.AddMonths(-1).Month).CopyToDatatable
1 Like

@supermanPunch Thanks but i should only get previous month that is Jan’24 value but i am getting entire Jan data previous years as well Please check


@manjusree_R_N_manju ,

Apologies again :sweat_smile:. Could you check with the below :

DT1  = DT.AsEnumerable.Where(Function(x)Not String.IsNullOrWhitespace(x("Month").ToString) andAlso CDate(x("Month").ToString).ToString("MMMyyyy") = Now.AddMonths(-1).ToString("MMMyyyy")).CopyToDatatable

Do also check with other Solutions what @Shiva_Nikhil and @pikorpa provided as I believe it does have the implementation correct.


Hi @manjusree_R_N_manju

→ Read Range Workbook
Output-> dt
→ Use below syntax in Assign:

filteredRows= dt.AsEnumerable().Where(Function(row) If(IsNumeric(row("Month").ToString()), DateTime.ParseExact(New DateTime(1900, 1, 1).AddDays(CInt(row("Month")) - 2).ToString("MMM-yy"), "MMM-yy", System.Globalization.CultureInfo.InvariantCulture).Year = DateTime.Now.AddMonths(-1).Year AndAlso DateTime.ParseExact(New DateTime(1900, 1, 1).AddDays(CInt(row("Month")) - 2).ToString("MMM-yy"), "MMM-yy", System.Globalization.CultureInfo.InvariantCulture).Month = DateTime.Now.AddMonths(-1).Month, DateTime.ParseExact(row("Month").ToString(), "MMM-yy", System.Globalization.CultureInfo.InvariantCulture).Year = DateTime.Now.AddMonths(-1).Year AndAlso DateTime.ParseExact(row("Month").ToString(), "MMM-yy", System.Globalization.CultureInfo.InvariantCulture).Month = DateTime.Now.AddMonths(-1).Month)).ToList()

filteredRows is of DataType System.Collections.Generic.List(System.Data.DataRow)
→ Use If condition and use below syntax:

  Assign-> dt= filteredRows.CopyToDataTable()
End If

→ Write Range Workbook
Sheet1 is Input and Sheet2-Output is Output sheet
Test (2).xlsx (12.2 KB)
Sequence3.xaml (10.0 KB)


1 Like

Thank you it is working as expected.

1 Like

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