sorting data within 90 days from today

[annadinesh]

Dear Experts

I am trying to Sort A List which are within 90 days with the following code but failed.


Range("A3:O65536").AutoFilter Field:=5, Operator:=xlAnd, _
Criteria1:="<" & Date - 90

Please check and help



Dinesh Saha

 

[AsifShah]

Dear Dinesh Saha Please Try This.

With Worksheets("Sheet1").Range("A1:O1").EntireColumn
        .AutoFilter
        .AutoFilter Field:=5, Criteria1:=">=" & Date - 90, Operator:=xlAnd, Criteria2:="<=" & Date
    End With

 

[annadinesh]

Dear Dinesh Saha Please Try This.

With Worksheets("Sheet1").Range("A1:O1").EntireColumn
        .AutoFilter
        .AutoFilter Field:=5, Criteria1:=">=" & Date - 90, Operator:=xlAnd, Criteria2:="<=" & Date
    End With

Sorry to say that the code is also not working,

My Data

Customer Name	Model	REGD NO.	Sale Date
Suk Bahadur Magar	Grand i10 NIOS 1.2MT Kappa Sportz	WB74BL3566	16.08.2022​
Tapan Kumar Pyne	Grand I10 Nios 1.2mt Kappa Sportz	WB72AB0597	27.06.2022
Biplob Sarkar	Grand I10 Nios 1.2mt Corporate	WB74BL0135	16.07.2022
Naresh Kumar	Creta 1.5 CRDi MT E	PB15X3020	12.08.2022​
Siddartha Kumar Pradhan	Venue 1.2 Kappa Mt S	SK05P3661	01.07.2022
Chandan Ghosh	i20 N Line N6 1.0 Turbo GDI iMT	WB74BL2023	17.08.2022​
Prabin Chandra Moktan	Creta 1.5 Mpi Ivt Sx	WB74BL0821	16.07.2022
Kamal Thapa	Grand I10 Nios 1.2mt Kappa Asta	WB74BL0597	23.07.2022
Partha Mallick	Venue 1.2 Kappa Mt Sx	WB74BL1004	25.07.2022
Phurtengy Sherpa	Creta 1.5 MPi MT EX	AF370715	19.08.2022​
Bikash Agarwal	Creta 1.5 MPi IVT SX(O) SE	WB74BL2324	24.08.2022​
Uma Sales	VENUE 1.5 CRDi MT SX	WB74BL1921	10.08.2022​
Swati Chakraborty	Creta 1.5 MPi MT S+ SE	WB74BL3299	19.08.2022​
Abhishek Saha	VERNA 1.5 MPI MT SX(O)	WB74BL6297	16.08.2022​
Kakali Bhattacharjee	Grand i10 NIOS 1.2 AMT Kappa Asta	WB74BL3514	12.08.2022​
Saria Construction	I20 Asta 1.2 Kappa Mt	WB74BL1551	29.07.2022
Shankar Lal Maheshwari	Alcazar 1.5 AT Signature(O) 7S	WB72AB2929	30.08.2022​
Pramod Pradhan	Venue 1.2 Kappa Mt S(o)	WB74BL1617	26.07.2022
Debabrata Ghosh	Creta 1.5 MPi IVT SX(O) SE	WB74BL1954	03.08.2022​
Arup Das	Venue 1.2 Kappa Mt S(o)	WB74BL2849	11.08.2022​
Karma Tenzing Dukpa	VENUE 1.2 Kappa MT SX	WB79B1300	08.08.2022​
Lakpa Norbu Sherpa	VENUE 1.2 Kappa MT S	WB74BL2676	16.08.2022​
Prajwal Nirola	i20 Asta(O) 1.2 Kappa MT	WB74BL5020	30.08.2022​
Goutam Bagchi	Grand I10 Nios 1.2mt Kappa Sportz	WB74BL2007	11.08.2022
Arpan Rajput	Grand i10 NIOS 1.2MT Kappa Sportz	WB74BL4020	12.08.2022​
Ganesh Sharma	VENUE 1.2 Kappa MT SX	WB74BL2846	12.08.2022​
Ashish Prakash	Venue 1.2 Kappa Mt Sx	BR50X1989	12.08.2022
Biswadeep Chowdhury	Venue 1.0 Turbo Imt Sx(o)	WB74BL5010	15.09.2022
Biswajit Chowdhury	VENUE 1.0 Turbo iMT SX(O)	WB74BL5010	15.09.2022​
Shankarshan Sarkar	i20 Sportz 1.2 Kappa MT	22BH2535C	11.08.2022​
Rashmita Thakuri	i20 Magna 1.2 Kappa MT	WB74BL4016	16.08.2022​
Amar Milan Chowhan	VENUE 1.2 Kappa MT S	WB74BL2840	12.08.2022​

I want when i press the command button the datas which are 90 days back from todays date will show in Coloumn SALE DATE.

Please Check and Help.

 

[HaHoBe]

Hi Dinesh Saha,

it's not clear by posting the code if it's Dates in there (right bound) or Dates formatted as Text (left bound).

Code for "real" dates could look like

        Range("A1").CurrentRegion.AutoFilter field:=4, Criteria1:=">=" & CLng(Date) - 90

To convert the dates you may try

Sub DoConvert()
  
  Dim rngCell     As Range

  For Each rngCell In Range("D2", Cells(Rows.Count, "D").End(xlUp))
    If IsNumeric(rngCell) And Not IsEmpty(rngCell) Then
      rngCell.Value = CDate(rngCell.Value)
    End If
  Next rngCell

End Sub

Both codes work on the active sheet in Excel.

Ciao,
Holger

 

[annadinesh]

Hi Dinesh Saha,

it's not clear by posting the code if it's Dates in there (right bound) or Dates formatted as Text (left bound).

Code for "real" dates could look like

        Range("A1").CurrentRegion.AutoFilter field:=4, Criteria1:=">=" & CLng(Date) - 90

To convert the dates you may try

Sub DoConvert()
 
  Dim rngCell     As Range

  For Each rngCell In Range("D2", Cells(Rows.Count, "D").End(xlUp))
    If IsNumeric(rngCell) And Not IsEmpty(rngCell) Then
      rngCell.Value = CDate(rngCell.Value)
    End If
  Next rngCell

End Sub

Both codes work on the active sheet in Excel.

Ciao,
Holger

ITS WORKING, THANKS A LOT