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

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
oups.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

ups.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 ?

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

oups.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

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

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.

--

Regards,
Tom Ogilvy


wrote in message
oups.com...
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