How to split and filter the date in excel

Help Activities
, ,
1

Hi all,

image
In New column 3 need to split the date, time has to be removed.
Example: 11-10-2023 4:00 PM

Output = 11-10-2023

and need to filter the date. New column 3 date has to be greater than or equal to 5 of current date.
Example:
09-10-2023 current date added with 5 then the output is

14-10-2023

End date filter.xaml (10.4 KB)
Extracted data.xlsx (12.6 KB)

Tried this method but its not working. Please guide me on this…

2

for this you can try this

CDate("11-10-2023 4:00 PM").ToString("MM-dd-yyyy")

@lakshmi.mp

3

Hi @lakshmi.mp

Check out this tutorial

You can try with Format cell activity to chnage the format of the date and time

image
image691×707 30.4 KB

Regards
Gokul

4

Hi @lakshmi.mp

Can you try with this expression

Dt_output= Dt_input.AsEnumerable.Where(Function(r) DateTime.Parse(r("Columname").ToString)>=New DateTime(Now.Year,Now.month,5)).CopyToDataTable
5

@Praveen_Mudhiraj
CDate(CurrentRow("New column 3").ToString).ToString("dd-MM-yyyy")
Used above code, need to remove the time from column “New column 3”.

Body: Conversion from string “13-10-2023 10:00 AM” to type ‘Date’ is not valid.
Getting this error.

6

Hi @lakshmi.mp

Try this

DateTime.ParseExact(CurrentRow("New column 3").ToString, "dd-MM-yyyy h:mm tt", System.Globalization.CultureInfo.InvariantCulture).ToString("dd-MM-yyyy")

7

try this one it will work

DateTime.ParseExact(CurrentRow("New column 3").ToString, "dd-MM-yyyy h:mm tt", System.Globalization.CultureInfo.InvariantCulture).ToString("dd-MM-yyyy")

image
image619×528 22.9 KB

@lakshmi.mp

8

Have you tried with Format cell activity?

it will format all the rows in the excel file

9

@Gokul001 ,
we are not using modern activities.

10

You can try this for removing the timestamp

DateTime.ParseExact(CurrentRow("New column 3").ToString, "dd-MM-yyyy h:mm tt", System.Globalization.CultureInfo.InvariantCulture).ToString("dd-MM-yyyy")

@lakshmi.mp

11

@Praveen_Mudhiraj ,

image
image1366×768 105 KB

12

you can see the output in the message box

image
@lakshmi.mp

13

@lakshmi.mp

dt_excel.AsEnumerable.Select(function(x) dt_excel.Clone.LoadDataRow({x(0).ToString,DateTime.ParseExact((x(1).ToString.Split(" "c).First),“dd-MM-yyyy”,System.Globalization.CultureInfo.InvariantCulture).AddDays(5).ToString(“dd-MM-yyyy”)},False)).copytodatatable

image
image1068×282 18.6 KB

by using above syntax,time in the date will be removed and it adds 5 days and re copied to datatable

14

Hi @lakshmi.mp

Check out this XAML file

09.10.2023_Forum_1.xaml (7.0 KB)

image
image1661×774 72.1 KB

Regards
Gokul

1 Like
15

You can try this xaml for time stamp removal

Xaml : - filter dates.zip (3.3 KB)

output : -

image
image544×640 7.22 KB

@lakshmi.mp

16

@Gokul001 , Able to remove the timestamp.
How to do filter for “New column 3”.
If the current date is 09-10-2023 now need to add 5 days to this date then the output will be 14-10-2023.
Current date + 5 days need to do.
current date + 5 days 09-10-2023 + 5 days => 14-10-2023
Now till the 13-10-2023 are not required need to remove…
For tomorrow it will be 10-10-2023 + 5days => 15-10-2023 till 14-10-2023 need to remove.
How to do this.

image
image1366×768 105 KB

But it’s not filtering. What changes needs to be done…

17

Hi @lakshmi.mp ,

So you want to remove Today+5 days, it means you need data after 5 days from today right?

Thanks,
Sandhiya P