How to extract previous Month date value from Excel

Help Studio
, ,
1

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.

image
image1905×1001 99.4 KB

Test.xlsx (11.2 KB)

1 Like
2

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.

3

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

image
image267×707 9.74 KB

4

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

image
image862×618 32.6 KB

5

@manjusree_R_N_manju

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

6

@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
7

@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

image

8

@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.

2 Likes
9

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:

If
  filteredRows.Any
Then
  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)

Regards

1 Like
10

@supermanPunch
Thank you it is working as expected.

1 Like
11

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