Need the find which dates are falling between start and end date

Help Activities
, , , , ,
1

My input

S.no. date. Value

  1.          12/31/2022. 45 

  2.         03/02/2022. 56

  3.          03/28/2022. 78

  4.            03/16/2022. 56

  5.            03/08/2022.  55

My start date is 03/01/2022 and end date is 03/31/2022. Dates falling between start and end is
My output should be

S.no. date. Value

  1.         03/02/2022. 56

  2.          03/28/2022. 78

  3.            03/16/2022. 56

  4.            03/08/2022.  55
2

@sruthesanju

dt.AsEnumerable().Where(Function(row) DateTime.ParseExact(row("date").ToString(), "MM/dd/yyyy", CultureInfo.InvariantCulture) >= startDate AndAlso DateTime.ParseExact(row("date").ToString(), "MM/dd/yyyy", CultureInfo.InvariantCulture) <= endDate).CopyToDataTable()
3

Hi @sruthesanju

Read Range (inputDataTable): "YourInputFile.xlsx"
Assign: startDate = DateTime.Parse("03/01/2022")
Assign: endDate = DateTime.Parse("03/31/2022")
Build DataTable (filteredDataTable): with the same structure as inputDataTable
ForEachrow in inputDataTable
    If: DateTime.Parse(row("date").ToString) >= startDate AndAlso DateTime.Parse(row("date").ToString) <= endDate
        Add DataRow: row to filteredDataTable

Write Range (filteredDataTable): "YourOutputFile.xlsx"

Hope it helps!!

4

@sruthesanju
Sequence
|-- Read Range (Input: “YourFilePath.xlsx”, Output: dtInput)
|-- Filter DataTable (Input: dtInput, Output: dtFiltered, Condition: “[date] >= ‘03/01/2022’ AND [date] <= ‘03/31/2022’”)
|-- Write Range (Input: dtFiltered, Output: “FilteredOutput.xlsx”)

5

Hi @sruthesanju

Try this:
→ Build Data Table
image
Output-> dt_Input
→ Use below syntax in Assign

Assign -> filteredDataTable = dt_Input.Clone()

Assign -> filteredDataTable = (From row In dt1_Input.AsEnumerable()
Where CDate(row("Date").ToString()) >= CDate("03/01/2022") AndAlso CDate(row("Date").ToString) <= CDate("03/31/2022")
Select row
   ).CopyToDataTable()

You can use Write Range Workbook to write filteredDataTable back to excel

image
image997×715 32.8 KB

Hope it helps!!

6

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