vba help - Sort Data as per Date Column

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
976
Office Version
  1. 2010
Platform
  1. Windows
Hi Team,

Need your help to sort Entire Data as per Invoice Date Column.
But Invoice date Columns are All not in Complete Date. plz help . Thanks


Below is a Data. sort data as per Criteria from Column A.

Book2
ABCD
1Invoice DateISNumberdata1Data2
210/12/2020TRUExxxyyy
310/12/2020TRUExxxyyy
410/12/2020TRUExxxyyy
513/06/2019FALSExxxyyy
613/08/2020FALSExxxyyy
713/08/2020FALSExxxyyy
813/08/2020FALSExxxyyy
913/10/2020FALSExxxyyy
1013/10/2020FALSExxxyyy
1113/10/2020FALSExxxyyy
1214/10/2020TRUExxxyyy
1313/10/2020FALSExxxyyy
1413/10/2020FALSExxxyyy
1511/10/2020TRUExxxyyy
1613/10/2020FALSExxxyyy
1713/10/2020FALSExxxyyy
1813/10/2020FALSExxxyyy
1913/10/2020FALSExxxyyy
Sheet1
Cell Formulas
RangeFormula
B2:B19B2=ISNUMBER(A2)


Thanks
mg
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
This appears to be a simple date data issue. Excel defaults to M/d/yyyy format for dates, so October 13, 2020 would be 10/13/2020 not 13/10/2020 which Excel will recognize as a string as it is not a valid date (there is no 13th month). You can change this behavior by selecting the column, left click it and select Format Cells... from the popup menu. In the Format Cells dialog, ensure Date is selected from the Category box, then under Locale (location) change it to the date format you want (English (Canada) has the d/M/yyyy format).
 
Upvote 0
Hi Rosen,

Thanks for highlighting it , got it, how to sort this issue via vba.
if you know vba as well.



Thanks
mg
 
Upvote 0
A cell's NumberFormat property can be set to the desired format, e.g. Columns("A:A").NumberFormat = "d/m/yyyy"

Though, I just realized that this isn't going to change a String value to a DateTime value. For that you will need to be a little more creative.

The following code will convert each cell in column A to a proper DateTime value based on its current M/d/yyyy format (you don't need to assign it back if manipulating the data in the spreadsheet isn't desired).

Code:
Sub ConvertToDateTime()
    Dim Row As Long
    Dim DateTimeString As String
    Dim DateTimeElements() As String
    Dim Year As Integer, Month As Integer, Day As Integer
    Row = 1
    Do Until Range("A" & Row).Value = VBA.Constants.vbNullString
        DateTimeString = Range("A" & Row).Text
        DateTimeElements = VBA.Strings.Split(DateTimeString, "/")
        Year = CInt(DateTimeElements(2))
        Month = CInt(DateTimeElements(1))
        Day = CInt(DateTimeElements(0))
        Range("A" & Row).Value = VBA.DateTime.DateSerial(Year, Month, Day)
        Row = Row + 1
    Loop
End Sub
 
Upvote 0
Hi Rosen,

Very nice ! Millions of thanks for your help. it worked (y) ?



Thanks
mg
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top