Programatically copy cells with 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

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.

 

[Dave Peterson]

Select the original range on sheet1
Give that range a nice format:
mmmm dd, yyyy

The cells that don't change to a nice long date aren't really dates--they're
text that just look like dates.

In fact, you may find that some of the real dates aren't what you expect.

Is 10-12-2005 really October 12, 2005 or December 10, 2005. With an unambiguous
format, you'll see it right away.

 

[Francis Brown]

Hello

Thanks for reply.

All the dates are in the format i mentioned. 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

any further input.

regards

Francis.

 

[Dave Peterson]

If you change the lines around:

Worksheets("Sheet2").Range("a8:b15").NumberFormat = "dd-mm-yyyy"
Worksheets("Sheet2").Range("a8:b15").Value _
= Worksheets("Sheet1").Range("a8:b15").Value

Does it work better?

If I had sheet2!a8:b15 formatted as text to start with, then I didn't get what I
wanted.

When I swapped the lines to format first, it worked ok.

Sometimes screwing with dates becomes lots safer if you use the .value2
property.

Worksheets("Sheet2").Range("a8:b15").NumberFormat = "dd-mm-yyyy"
Worksheets("Sheet2").Range("a8:b15").Value2 _
= Worksheets("Sheet1").Range("a8:b15").Value2

..value2 will use the serial date (like 38696). Then applying the numberformat
in either order was ok.

 

[Francis Brown]

I also found a similar method.

Set sheet one number format to general
Do the copy
Set both sheets number formats back to dd-mm-yyyy.

This works.

I wasnt aware of value2 setting and will read up on this.

Thanks for input.