date format

[Nasir.Munir]

I have a problem displaying date.
Situation: worksheet has a column which is displaying date wrongly.
For example it is displaying year/month/date. When it displays date it
is adding either 20 or 30 with the date. ie 06/09/2029. Here 2029
should be 29 as a day.
At any rate, I have written a macro which parses the cell and chop off
the 20 part, and stores the value as 06/09/29.
Problem: when I try to display the value back into sheet, it changes
the format to 09/06/2029, instead of displaying 06/09/29. Can anyone
help ?
My codenothing wrong with it, works fine, but cant display the value
back to worksheet)
Sub formatDate()

hold = ThisWorkbook.Sheets("Sheet2").Cells(2, 13).Value
Debug.Print hold
hold_values = Split(hold, "/")
recombine = ""
For i = 0 To 2 ' format correction

If i = 2 Then
chop = Right(hold_values(i), 2)
Debug.Print chop
recombine = recombine + chop
Else
Debug.Print hold_values(i)
recombine = recombine + hold_values(i) + "/"

End If

Next i

ThisWorkbook.Sheets("Sheet2").Cells(2, 16).Value = recombine
Debug.Print recombine ' displays the result as required ie YY/MM/DD


End Sub

 

[Bob Phillips]

Maybe

With ThisWorkbook.Sheets("Sheet2").Cells(2, 16)
.NumberFormat = "dd/mm/yy"
.Value = recombine
End With

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Nasir.Munir]

Maybe

With ThisWorkbook.Sheets("Sheet2").Cells(2, 16)
.NumberFormat = "dd/mm/yy"
.Value = recombine
End With

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

it worked, but there is still a problem.
it is displaying as mm/yy/dd
The original value in sheet is 07/02/2005 ie yy/mm/dddd where dddd is
wrong should be dd.
When I use ur suggestion it is displaying as 02/07/05 ie mm/yy/dd. I
want that to be displayed as 07/02/05. Can you suggest something
again, thanks

 

[Nasir.Munir]

Maybe

With ThisWorkbook.Sheets("Sheet2").Cells(2, 16)
.NumberFormat = "dd/mm/yy"
.Value = recombine
End With

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)












- Show quoted text -

I am not sure if it is right, but i have tried this:
With ThisWorkbook.Sheets("Sheet2").Cells(2, 16)
.NumberFormat = "mm/dd/yy"
.Value = recombine
End With
I changed numberFormat to mm/dd/yy from dd/mm/yy
It is working fine, can you explain that ?

 

[Tom Ogilvy]

VBA interprets date strings using US english conventions if possible.

Cdate(recombine)

would intepret the string using regional settings.

 

[Nasir.Munir]

VBA interprets date strings using US english conventions if possible.

Cdate(recombine)

would intepret the string using regional settings.

--
Regards,
Tom Ogilvy







- Show quoted text -

Not again........sorry guys, the problem is still there. I can display
the required result as yy/mm/dd, but when I try to change the cell
format to date ie say March 14, 2005, I have the wrong answer. It
displayes 07/02/05 as July 2, 2005. It should be Feb 5, 2007.
I need help, as I am totally lost. Have tried many ways, no success.
Thanks

 

[Nasir.Munir]

Not again........sorry guys, the problem is still there. I can display
the required result as yy/mm/dd, but when I try to change the cell
format to date ie say March 14, 2005, I have the wrong answer. It
displayes 07/02/05 as July 2, 2005. It should be Feb 5, 2007.
I need help, as I am totally lost. Have tried many ways, no success.
Thanks- Hide quoted text -

- Show quoted text -

the correct way should be to store the values in an array, and then
use datevalue() to get the required results

 

[Tom Ogilvy]

For anyone that wants the real answer,

Datevalue and cdate would give identical results for the same string.


this is explained in this extract from Stephen Bullens book on international
issues:

http://www.oaltd.co.uk/ExcelProgRef/ch22/

CDate(), DateValue()
These methods can convert a string to a Date data type (CDate can also
convert other data types to the Date type). The string must be formatted
according to WRS [Windows Regional Setting] and use the Windows language for
month names. It does not recognize the names for the days of the week,
giving a Type Mismatch error. If the year is not specified in the string, it
uses the current year.