Entering date as "DD/MM/YYYY@;" but result is with "DD-MM-YYYY"

[NimishK]

Hello
Trying to enter Date as .NumberFormat = "dd/mm/yyyy;@" but result displays as "dd-mm-yyyy" Dont understand why

below for the reference

[COLOR=#ff0000][I]Code in thisWorkbook[/I][/COLOR]
Option Explicit


Private Sub Workbook_Open()
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
ws.Range("A:A").NumberFormat = "dd/mm/yyyy;@"
End Sub

[COLOR=#ff0000][I]code in sheet2[/I][/COLOR]
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
Dim dDate As Date
Dim thisRow As Long

If Target.Column = 1 Then
   thisRow = Target.Row
   If Not Target.Range("A" & thisRow).NumberFormat = "dd/mm/yyyy;@" Then
       MsgBox "No Correct date"
    Else
      On Local Error GoTo errMsg:
      dDate = Format(ws.Cells(thisRow, 1).Text, "dd/mm/yyyy;@")
      Range("B" & thisRow).Value = dDate
      
errMsg:
MsgBox "Pl Enter Date in dd/mm/yyyy format " 
Exit Sub
     
   End If
End If
End Sub

NimishK

 

[NimishK]

Rick

BTW there is already checked in TAB bydefault do you also want me to uncheck.

Mark and Rick as per your suggestions nothing happened
Also coded in completely new file nothing happened

 

[MARK858]

Is your Windows regional date setting dd/mm/yyyy?

 

[Rick Rothstein]

Rick

BTW there is already checked in TAB bydefault do you also want me to uncheck.

That checkbox does not matter... the only reason I wanted you to uncheck the "Other" checkbox was in case its data field had a dash in it (that would have screwed up what Mark was trying to have you do).

Mark and Rick as per your suggestions nothing happened
Also coded in completely new file nothing happened

The only reason I can think why that would not have worked is if your dashes are not ASCII dashes. For a cell with one of your text dates in it (I am assuming cell A2... change if that guess is incorrect), put this formula in a blank cell and tell us what it displays...

=CODE(MID(A2,3,1))

 

[NimishK]

Mark

Yes, Went in windows Regional setting and the Format there was dd-mm-yyyy
I changed to dd/mm/yyyy and now both the columns is having all the dates with dd/mm/yyyy. Again Stuck because my new thread heading would be
Entering date as "DD-MM-YYYY@;" but result is with "DD/MM/YYYY"


Any macro for to change windows regional Date settings to what i desire.
Then it is no use coding for full column as .numberformat "dd/mm/yyy;@" as this nothing happens because of Windows Regional Date Settings

Rick
=CODE(MID(A2, 3,1)) gave me #Value