Sort according to the month in a excel sheet

I have excel sheet which contain a column with date in the format of “09/01/2018 09:25:19” How to I sort The details for last month i.e today is 23rd I need to get the details beween 23rd april to 23rd may. And also sort them 23-5-2018 to today’s date

The format is “dd/mm/yyyy hh:mm:ss”

I need to write into another excel sheet for only a month and fir only year.

Hello,

If you can provide the excel I can create a workflow that will help

Regarding to the solution, you can use DateTime.compare to filter your dates according to what interval you like (ex between 23rd aprid and 23rd may)
And regarding to the sorting method I also this there is a function to take care of that for you

Book1.xlsx (15.6 KB)

Here I want for only last month in one excel sheet and for last year in one excel sheet

@saandra Sorry for the wait time, but I’ve made you a flow.
I’ve changed the month to last 3 months just to test if it’s working, you can change it how you like

Main.xaml (8.0 KB)

I need the criteria of last month and date only for Created time and rest of the rows also present with them.

In the example you gave It is calculating for both creation and closed

" d1 = DateTime.ParseExact(row(“Created”).ToString.Split({" "},stringsplitoptions.None)(0),“MM/dd/yyyy”,CultureInfo.InvariantCulture) "

The line above is the one that is extracting the date used in the filtering, as you can see row(“Created”) is using the date only from the “Created” column to filter the results

But when I executedBook1.xlsx (18.4 KB)
some closed rows are missing in the month sheet

I don’t understand what do you mean by “some closed rows are missing”

Sorry, Every thing is correct but can you tell me how to change the format of the closed and created column to same as the input excel “5/30/2017 10:32:17 AM”.

You’ll need to rewrite the entire sheet for that, so

Read the Sheet1 in a DataTable
Use a for each row to loop through the table
Now comes the tricky part, you need to extract each date from the row, convert it into a date time and then rewrite it as a string
for example:

for each row as DataRow in DataTable.Rows
date1 = DateTime.ParseExact(row(“Created”).tostring,"dd/MM/yyyy,CultureInfo.InvariantCulture)
date2 = DateTime.ParseExact(row(“Closed”).tostring,"dd/MM/yyyy,CultureInfo.InvariantCulture)
row("Created) = date1.tostring("MM/dd/yyyy)
row("Closed) = date2.tostring("MM/dd/yyyy)
next

When I’m using Closed column in the Code I’m getting this error

It’s not working because some of the rows are empty. Can you tell me how ingnore empy rows.

I’ve solved it by using
If(row(“closed”).ToString <> “”) Then
d1 = DateTime.ParseExact(row(“Closed”).ToString.Split({" "},stringsplitoptions.None)(0),“MM/dd/yyyy”,CultureInfo.InvariantCulture)
End If

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.