Filter yesteday's date in Excel

Help StudioX
,
1

Hi, I would like the robot in Excel, in a column, to filter yesterday’s date, in the format dd.MM.yyyy - can you help? :slight_smile:

2

@sullivanne,

Use below code to filter the data by reading it with Read Range Activity.

Use this code in Assign activity

dt_Output = Input_dt.AsEnumerable().Where(Function(X) DateTime.ParseExact(X("Column name").ToString(), "dd.MM.yyyy", System.Globalization.CultureInfo.InvariantCulture) = DateTime.Now.AddDays(-1)).CopyToDataTable()
1 Like
3

@sullivanne

If needed directly on excel then use filter excel activity

If not then read the data and can use the below…remember when your ead the data into datatable the format of date can change…so check the same in locals pnel using debug mode

dt_Output = Input_dt.AsEnumerable().Where(Function(x) Cdate(c("Column name").ToString) = Now.AddDays(-1)).CopyToDataTable()

Cheers

1 Like
4

Thank You very much, but I have error: String ‘10/27/2024’ was not recognized as a valid DateTime :frowning: Do You have any ideas?

5

@sullivanne

in the above formula change dd.MM.yyyy to MM/dd/yyyy

cheers

1 Like
6

@sullivanne,

This should work:

dt_Output = Input_dt.AsEnumerable().Where(Function(X) DateTime.ParseExact(X("Column name").ToString(), "MM.dd.yyyy", System.Globalization.CultureInfo.InvariantCulture) = DateTime.Now.AddDays(-1)).CopyToDataTable()

You faced this error because LINQ was for dd.MM.yyyy format but the date being passed in MM.dd.yyyy format

1 Like
7

Thank you for your advice, but unfortunately still the same error :frowning:

8

@sullivanne,

Can you recheck what format data you have in the column is it dd.MM.yyyy or MM.dd.yyyy

I suspect there is both kind of format which is resulting in this. Try taking few row as sample and do the operation to verify this.