Vba Code for sorting date

[Barbarano]

Hello all,

I am a beginner learner of VBA, and I am not able to solve this issue.
I am trying to automatize a report, and I need to create an user-interface to sort out some date.

The column L is the one which needs to be sorted.

I am in need of allow the user to filter even multiples dates within it.

A major issue which I run into is that the format is mm/dd/yyyy followed by the time, and the time seems to be sometimes which interfere with the filter option. I`ve even tried to separate it through the text to column option but the time remains stick to the date.
This is the part of vba code I was trying to scribble:

See the images attached for more details about it.
Any suggestion with it? Many thanks for your time.

[Barbarano]

In order to respect the post-cross netiquette rule, I`m informing whoever is reading this that I`ve posted the same question in this other forum here https://www.excelforum.com/excel-pro...ting-date.html

[Paul_Hossler]

In order to respect the post-cross netiquette rule, I`m informing whoever is reading this that I`ve posted the same question in this other forum here https://www.excelforum.com/excel-pro...ting-date.html

1. Thank you for that

2. Can you attach a small sample workbook? It makes it easier to see and to test.

Sometimes the actual data is important, since for example, "2/10/2018 12:00:00 AM" might be a string of it might just be the way the data is formatted for display

[Barbarano]

Thank you Paul_Hossler, I have attached a sample of the mentioned file.

As you wrote, the time cell is set as string, and as someone else suggested in the same topic in the other forum, an ideal thing would be to convert this string in a number (which represent a number from the 1st of January 1900 till today) and creating a way for filtering this number ... I will try to figured it out.

[Paul_Hossler]

Title says 'Sorted' but examples show filtering ???


To filter a date range, you can use something like this

'example - all dates between 5/25/2018 and 5/29/2018

Sub Test()
    Dim D1 As Date, D2 As Date
    
    D1 = DateSerial(2018, 5, 25)
    D2 = DateSerial(2018, 5, 29) + 1
    
    ActiveSheet.Rows(1).AutoFilter
    'Note the >= on critera1 and the < on critera 2
    ActiveSheet.Cells(1, 1).CurrentRegion.AutoFilter Field:=12, Criteria1:=">=" & D1, Operator:=xlAnd, Criteria2:="<" & D2
End Sub

[SamT]

Copy Column L (except the Header) to an unused sheet. Format the entire Column Cells As Number = General. Also change the Number Format of Column L on the original sheet to General.

I'm assuming that you copied it to Column L on the unused sheet, Change the Range Address in the code below to suit the actual column. Run the Code,. Paste the Resulting Column L back onto the Original Sheet, then Format that Column as Number = Date of the style you prefer.

This code goes in the "Unused Sheet" code page

Sub MakeRealDates()
Dim Cel As Range

For Each Cel in Range("L1").CurrentRegion
  Cel.Value = CDbl(CDate(Cel.Value))
Next

End Sub

The results of that Sub should be 5 digit numbers followed by at least 10 or 11 decimal digits. Check them. Any that failed just could not be read as a date, either a Text or a numerical date.

Be sure to experiment on a copy, not your original working file.

Since I don't know how your Date Data is entered, and you don't know if it is entered as a String or as a Date, Place this code in the Original Sheet's Code Page. It will convert all String Dates to Numerical Dates for Sorting/Filtering.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Columns("L")) Is Nothing Then MakeRealDate Intersect(Target, Columns("L"))
End Sub

Private Sub MakeRealDate(Rnge As Range)
Dim Cel As Range
Dim Val
Static Frmat
Frmat = Range("L2").NumberFormat 'Always insure that L2 is properly formatted

For Each Cel In Rnge
  With Cel
    If Cel.Address = "$L$1" Then GoTo CelNext
    If Not IsNumeric(.Value2) Then
       Val = .Value2
      .Value2 = CDate(Val)
    End If
    .NumberFormat = Frmat
  End With
CelNext:
Next
End Sub

If you google, you can probably find the code for the NumberFormat of the Date Format you want and change Frmat to a constant and not have to worry about the Format of L2.

[Barbarano]

[QUOTE=Paul_Hossler;380651]Title says 'Sorted' but examples show filtering ???

My mistake, I meant filtering.

Thank you all for your contribution ... however I am still working on it.

[Barbarano]

Hello all,

First of all, thank you very much for your help and contribution.
After having solve the date format problem, another issue has arisen.
I want to create an input box which allow the user to filter the date, and I wrote this piece of script:



Dim Daterange As Date

Daterange = InputBox("Please enter effective date (mm/dd/yy)")
ActiveCell.Columns("L:L" & Lastrow).AutoFilter Field:=4, Operator:= _
xlFilterValues, Criteria1:=Format(Daterange, "mm/dd/yy;")

End Sub

however it doesn`t filter for the typed value. My doubt is: should Daterange set as Date or should it be set as an array ?

[Barbarano]

Hello all,

at the end I have been solving my issue in this way: I have added a sheet (and then I will include this section in the main sheet) where the user can insert a range of 2 dates in 2 cells, and the macro can eventually filters the column dates according to these 2 values:
Public Sub MyFilter()
Dim lngStart As Long, lngEnd As Long
Sheets("sheet1").Select
lngStart = Range("A1").Value 'assume this is the start date
lngEnd = Range("A2").Value 'assume this is the end date
Sheets("Report_Source").Select
Range("L:L").AutoFilter field:=1, _
Criteria1:=">=" & lngStart, _
Operator:=xlAnd, _
Criteria2:="<=" & lngEnd
End Sub

Again: thank you all for your time. I will put the status "solved" in the topic.