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

 

[jimrward]

dDate=Format ... line are you sure this is formatting the cell or your date string, try adding number formatting to the cell range(“b1”).numberformat=

 

[NimishK]

JimrWard

dDate=Format ... line are you sure this is formatting the cell or your date string, try adding number formatting to the cell range(“b1”).numberformat=

Sorry Nothing happens as per your suggestions

 

[MARK858]

JimrWard


Sorry Nothing happens as per your suggestions

What exactly is in ws.Cells(thisRow, 1)? and why if it is a date are you using .Text rather than .Value?

 

[NimishK]

Mark858

Nothing happens

What exactly is in ws.Cells(thisRow, 1)? and why if it is a date are you using .Text rather than .Value?

i changed to value from text and
I suppose as per coding the format in ws.Cells(thisRow, 1) .Value is the Date eg 21-10-2001
in col B it just replicas as per col A and does not format as "dd/mm/yyyy;@"

 

[MARK858]

In an empty cell put the formula =ISNUMBER(A2) changing the A2 to the cell reference of ws.Cells(thisRow, 1), does it return True or False?


and what happens if you change

dDate = Format(ws.Cells(thisRow, 1).Text, "dd/mm/yyyy;@")
      Range("B" & thisRow).Value = dDate

to

Range("B" & thisRow).NumberFormat = "General"
Range("B" & thisRow).Value = ws.Cells(thisRow, 1).Value
Range("B" & thisRow).NumberFormat = "dd/mm/yyyy;@"

 

[NimishK]

In an empty cell put the formula =ISNUMBER(A2) changing the A2 to the cell reference of ws.Cells(thisRow, 1), does it return True or False?

it returns as FALSE when =ISNUMBER(A2) because my whole column"A" is with dd-mm-yyyy ws.Range("A:A").NumberFormat = "dd/mm/yyyy;@"

Range("B" & thisRow).NumberFormat = "General"
Range("B" & thisRow).Value = ws.Cells(thisRow, 1).Value
Range("B" & thisRow).NumberFormat = "dd/mm/yyyy;@"

tried above nothing happens. May i request you to try the same at your End

 

[MARK858]

May i request you to try the same at your End

I already know what happens at my end, it is what happens at your end that matters.

it returns as FALSE when =ISNUMBER(A2) because my whole column"A" is with dd-mm-yyyy

So what you have there is text not a real date.

tried above nothing happens.

How can nothing happen, you must have had something appear in Range("B" & thisRow)?

 

[MARK858]

Select column A, click Data - click Text To Columns - click Next twice and click Finish. What happens to column A?

 

[Rick Rothstein]

Select column A, click Data - click Text To Columns - click Next twice and click Finish. What happens to column A?

I would change that slightly...

Select Column A, click "Data" tab - click "Text To Columns" - select "Delimited" and click "Next", make sure "Other" is not checked and click "Next", select "Date" option and change its dropdown to DMY and click "Finish".