Need to check if the dates falling between start and end date in datatable

[sruthesanju (Sweety)]

My input date is
06/30/2023

My input datatable is

Start Date. End date. Value

1/1/2021. 12/31/2021 45
1/1/2022. 12/31/2022. 65
1/1/2023. 12/31/2023. 56

My date 06/30/2023. Falls in last row so my output should be

Start Date. End date. Value
1/1/2023. 12/31/2023. 56

[Parvathy (PS Parvathy)]

Hi @sruthesanju

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

result_dt = dt.AsEnumerable().Where(Function(row) DateTime.Parse(row("Start Date").ToString()) <= Cdate("06/30/2023") AndAlso Cdate("06/30/2023") <= DateTime.Parse(row("End date").ToString())).CopyToDataTable()

→ Write Range Workbook result_dt

Hope it helps!!

[lrtetala (Lakshman Reddy)]

Hi @sruthesanju

InputDate = DateTime.ParseExact("06/30/2023", "MM/dd/yyyy", CultureInfo.InvariantCulture)


DT = (From row In DT.AsEnumerable()
                   Where row.Field(Of DateTime)("Start Date") <= InputDate AndAlso InputDate <= row.Field(Of DateTime)("End Date")
                   Select row).CopyToDataTable()

Main.xaml (11.9 KB)

Input:

Output:

Regards,

[rlgandu]

@sruthesanju

YourDataTable.AsEnumerable().Where(Function(row) DateTime.ParseExact("06/30/2023", "MM/dd/yyyy", CultureInfo.InvariantCulture) >= DateTime.ParseExact(row("Start Date").ToString(), "M/d/yyyy", CultureInfo.InvariantCulture) AndAlso DateTime.ParseExact("06/30/2023", "MM/dd/yyyy", CultureInfo.InvariantCulture) <= DateTime.ParseExact(row("End date").ToString(), "M/d/yyyy", CultureInfo.InvariantCulture)).CopyToDataTable()

Import System.Globalization

[Shiva_Nikhil]

@sruthesanju

try this

FinalOutput = DataTable.AsEnumerable().Where(Function(row) Cdate("06/30/2023") >= DateTime.Parse(row("Start Date").ToString())  AndAlso Cdate("06/30/2023") <= DateTime.Parse(row("End date").ToString())).CopyToDataTable()

[Parvathy (PS Parvathy)]

Hi @sruthesanju

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

result_dt = dt.AsEnumerable().Where(Function(row) DateTime.Parse(row("Start Date").ToString()) <= Cdate("06/30/2023") AndAlso Cdate("06/30/2023") <= DateTime.Parse(row("End date").ToString())).CopyToDataTable()

→ Write Range Workbook result_dt

Hope it helps!!

[mkankatala (Mahesh Kankatala)]

Hi @sruthesanju

→ Use Read range workbook activity to read the excel and store in a datatable called Input_dt.
→ You can use the below LinQ Expression,

- Assign -> Ouput_dt = (From row In Input_dt
                        Where (CDate(row("Start Date"))<CDate("06/30/2023")) AndAlso ((CDate(row("End date")))>CDate("06/30/2023"))
                        Select row
                             	).Copytodatatable()

→ Use Write range workbook activity to write the Output_dt datatable to excel.

Hope it helps!!