Copy Dates

[Francis Brown]

I Start with the following dates in the range a8 to b15 in the dd-mm-yyyy
format.

10-12-2005 16-12-2005
03-12-2005 09-12-2005
26-11-2005 02-12-2005
19-11-2005 25-11-2005
12-11-2005 18-11-2005
05-11-2005 11-11-2005
29-10-2005 04-11-2005
22-10-2005 28-10-2005

If you change the cell format to general the excel date codes are all
correct.

like this

38696 38702
38689 38695
38682 38688
38675 38681
38668 38674
38661 38667
38654 38660
38647 38653

so I know the dates are not psudo text formats etc.

I then run the following code,

Public Sub copydates()
Worksheets("Sheet2").Range("a8:b15").Value =
Worksheets("Sheet1").Range_("a8:b15").Value
Worksheets("Sheet2").Range("a8:b15").NumberFormat = "dd-mm-yyyy"

End Sub

Which result in the following on the second sheet

12-10-2005 12/16/2005
12-03-2005 12-09-2005
11/26/2005 12-02-2005
11/19/2005 11/25/2005
11-12-2005 11/18/2005
11-05-2005 11-11-2005
10/29/2005 11-04-2005
10/22/2005 10/28/2005

Can someone please explain what excel is doing wrong or more lickley what am
I doing wrong. why if the dates are recrded in the original serial format
correctly in the celld does excel mess up the order.

? could this be something to so with the uk/us date settings on the computer
- read this other post. I'm on uk settings XP and Excel 97.

Francis.

Note: this is a repost after update of question. Thanks to Dave Peterson for
input in previous post

 

[Francis Brown]

Hello All

Discovered the following works.

Public Sub copydates()
Worksheets("Sheet1").Range("a8:b15").NumberFormat = "General"
Worksheets("Sheet2").Range("a8:b15").Value =
Worksheets("Sheet1").Range("a8:b15").Value
Worksheets("Sheet2").Range("a8:b15").NumberFormat = "dd-mm-yyyy"
Worksheets("Sheet1").Range("a8:b15").NumberFormat = "dd-mm-yyyy"
End Sub

Must be excel just not picking up the date value properly if cell has a date
format applied. Removing the format first, applying the copy and then
reinstating the date formats on both sheets works.

Regards

Francis.

 

[Dave Peterson]

You have a reply at your initial post.

Hello All

Discovered the following works.

Public Sub copydates()
Worksheets("Sheet1").Range("a8:b15").NumberFormat = "General"
Worksheets("Sheet2").Range("a8:b15").Value =
Worksheets("Sheet1").Range("a8:b15").Value
Worksheets("Sheet2").Range("a8:b15").NumberFormat = "dd-mm-yyyy"
Worksheets("Sheet1").Range("a8:b15").NumberFormat = "dd-mm-yyyy"
End Sub

Must be excel just not picking up the date value properly if cell has a date
format applied. Removing the format first, applying the copy and then
reinstating the date formats on both sheets works.

Regards

Francis.

 

[Francis Brown]

Thanks for Input.

Working now.

Francis

You have a reply at your initial post.