How to select and Pick up value based on date given


Need help to search value based on given date.
I have a table as below

Year Month Week start Week end Product A Product B Product c Product d
2018 November 01/11/2018 07/11/2018 2.2 2.81 2.18 2.28
2018 November 08/11/2018 14/11/2018 2.2 2.81 2.18 2.28
2018 November 15/11/2018 21/11/2018 2.2 2.81 2.18 2.28
2018 November 22/11/2018 28/11/2018 2.2 2.81 2.18 2.28
2018 November 29/11/2018 05/12/2018 2.2 2.5 2.18 2.28

when A given date is for example 24/11/2018 and product purchase is product B- I need to find the value for that product which is fall under date range between 22/11/2018 and 28/11/2018 and the value that i need to pick up is 2.81. This value not fixed it can be change based on date.
How can I get specific value based on date given.
Really need a help on this

Thank you

Is the data stored in Excel Sheet ?

Hi KarthikByggari,


Thank you.

Load the Excel data into Data Table using Read Range Activity.
And use filter data table activity to filter the data between the dates.

How can I filter date in data table? Do i need to convert the date to date or convert to string?

The column in the data table must be in Date data type. Then only the filtering will work.
Make sure the data type will be Date for both Week Start and Week End columns.

meaning column in the excel for week start and week end must be date format. And from filter table how can I pass variable for date

Store the date in a variable which is in DateTime data type.
In the Filter Data Table activity, just pass the variable which contains Date.

For creating date -

dtDate = new DateTime(2019, 8, 20)

can you give me sample xaml file

Will send you after a while.

thank you very much for your KarthikByggari

Hi Karthik,

can you help me on this.