Sort column issue in one cell

[ipbr21054]

Evening,
I have two macros of which one sorts column G Ascending & the other Descending.
The column in question is date format.
The code in use is supplied below.
I use the same code for both & just changed one to Descending etc.
My problem is that cell G4 when Descending is incorrect, Ascending is fine.

Example
G4 =18/09/2017
G5 =03/11/2017
G6 =30/10/2017
G7 =27/09/2017

One thing i did notice is the date in cell G4 shows an upside down v before the date where other cells just show the date,if that helps.

Private Sub SortDateoldnew_Click()    
    Dim x As Long
    
    Application.ScreenUpdating = False
    
    With Sheets("HONDA LIST")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        .Range("A3:G" & x).Sort Key1:=Range("G4"), Order1:=xlAscending, Header:=xlGuess
        
    End With
                      
    ActiveWorkbook.Save
    
    Application.ScreenUpdating = True
    Sheets("HONDA LIST").Range("A4").Select
    
End Sub

 

[ipbr21054]

I went to address bar and removed the symbol.
When I sort it’s srill incorrect.
If I enter a date just say 3 months before it’s fine.
I’ve tried to delete the row and enter it on a new row.
Deleted cell contents then copy and paste from elsewhere.
Same issue.

I will continue to play with it.

 

[thisoldman]

If the bad date is in cell G4, what does this formula return?

=CODE(LEFT(G4))

If you are using an older version of Excel, check the options for Lotus compatibility settings.

The prefixed caret (^) is an old, Lotus 1-2-3 method to center-align text in a cell. The apostrophe (') was used for left-alignment, the backslash (\) forced right-alignment. These character-prefix methods worked in both the terminal display and the WYSIWIG versions of Lotus 1-2-3. I believe they worked in Excel, too. I seem to recall using them in Excel 4 and 5, and perhaps even in Excel 95.