date format
On Feb 13, 1:27 pm, wrote:
On Feb 13, 11:32 am, "Tom Ogilvy" wrote:
VBA interprets date strings using US english conventions if possible.
Cdate(recombine)
would intepret the string using regional settings.
--
Regards,
Tom Ogilvy
wrote in message
roups.com...
On Feb 13, 10:19 am, "Bob Phillips" wrote:
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)
wrote in message
roups.com...
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 code:(nothing 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- Hide quoted text -
- 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 ?- Hide quoted text -
- 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- 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
|