How to filter datetime

today’s query is how to filter datetime column in excel. here is the reference file.
I try from my side but not get any resut.

filter column (“Action_time”)

New Microsoft Excel Worksheet (2).xlsx (8.9 KB)

please help

Hi @Lakshya_Garg2,

What are we trying to filter on the table?

I need to filter column “Action_time” in which i want last hour data
New Microsoft Excel Worksheet (2).xlsx (9.2 KB)

Hi,
You can try this way
readDt.AsEnumerable.Where(Function(x)x(“Action_time”).ToString.Contains(“2022”)).CopyToDataTable

i want only last hour data

HI @Lakshya_Garg2

Can you share the sample Input and Expected output

Regards
Gokul

New Microsoft Excel Worksheet (2).xlsx (10.9 KB)

this is the output

HI @Lakshya_Garg2

How about this LINQ?

DtRead.AsEnumerable.Where(Function(x) DateTime.ParseExact(x("Action_time").ToString,"MM/dd/yyyy HH:mm:ss",System.Globalization.CultureInfo.InvariantCulture).ToString("HH:mm")<=(Now.AddHours(-1).AddMinutes(-60).ToString("HH:mm"))).CopyToDataTable

Check out the XAML file

FilterLastHourDate.xaml (9.9 KB)

Regards
Gokul

It’s not working.

read_dt.AsEnumerable.Where(Function(x) DateTime.ParseExact(x(“Action_time”).ToString, “MM/dd/yyyy HH:mm:ss”,System.Globalization.CultureInfo.InvariantCulture).ToString(“HH:mm”)<=(Now.AddHours(-1).AddMinutes(-60).ToString(“HH:mm”))).CopyToDataTable

it’s shown that errror:

Check out this XAML file @Lakshya_Garg2

@Lakshya_Garg2

You have missed the ,

image

Check this screenshot

image

I am getting this error

Assign: For getting last hour data: String was not recognized as a valid DateTime.

here is the code: Dt_Crm.AsEnumerable.Where(Function(x) DateTime.ParseExact(x(“Action_time”).ToString,“dd-MM-yyyy HH:mm:ss”,System.Globalization.CultureInfo.InvariantCulture).ToString(“HH:mm”)<=(Now.AddHours(-1).AddMinutes(-60).ToString(“HH:mm”))).CopyToDataTable

please help

Hi @Lakshya_Garg2

Can you share the input for this column Action_time ?

New Microsoft Excel Worksheet (2).xlsx (10.9 KB)

Hi @Lakshya_Garg2

Check out the XAML file

FilterLastHourDate.xaml (11.0 KB)

Regards
Gokul

same error occured on ur code.

here is some change in date format in input file:
date is in “dd-MM-yyyy” format.

when I am put this format that shown an error i.e. i already mention above

HI @Lakshya_Garg2

In my environment it is working with out any error.

Check out this SS

Regards
Gokul

can you try to write this on excel

I am facing that error during running code

Assign: For getting last hour data: String was not recognized as a valid DateTime.

@Lakshya_Garg2 ,
It means you are passing wrong string format to convert string as Date
For example, 22/11/2022 → dd/MM/yyyy, If you pass MM/dd/yyyy, you face error.
So check your string format