A little date formatting puzzle

[Cath]

Hi

Just got my array to work (thanks for the help everyone)

However I take a date, eg, 17/11/05 (format dd/mm/yy), it goes into an array, and if I check the value it is still 17/11/05. It is then entered into a cell (who's format is dd/mm/yy), but when I look at the results, it says 11/17/05 but strangely the format is still dd/mm/yy??

Has anyone any idea how to change this? I could understand if it had defaulted to m/d/y as I could just change it back, but now it actually has the wrong date in?

Thanks

Cath

 

[Cath]

Nope, its still putting it in as 11/17/2005. I'm thinking I have to go back to the original sheet, pick up the date again and put it in the right cell avoiding arrays altogether, although not what I wanted to do really



Cath

 

[BBHRVS]

Hi;

Where your date is coming from ?
If it is coming from a userform, there is a conflict between the latin and the anglo saxon format.
Then you have to enter into your code :
Range(xx).Value = DateSerial(Year(mydate.Value), Month(mydate.Value), Day(mydate.Value))
You can also use this to change your date into the good one.

Hope this helps.

 

[Cath]

Hi

The date is being picked up from a cell in an excell sheet.

Cath

 

[BBHRVS]

Try this, linked to a button, or with a combination of keyboard keys (e.g. ctrl+a).

Sub copydate()
On Error GoTo fin
Set rep = Application.InputBox("Click on the cell in which the date you want to copy is", _
Title:="Copy date", Type:=8)
Set rep2 = Application.InputBox("Click on the cell in which you want to paste your date", _
Title:="Paste date", Type:=8)
rep2.Value = DateSerial(Year(rep.Value), Month(rep.Value), Day(rep.Value))
Exit Sub

fin:
MsgBox "Copy cancelled by user", vbInformation + vbOKOnly, "Stop copy"
End Sub