|
Copy Dates
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 |
Copy Dates
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" wrote: 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 |
Copy Dates
You have a reply at your initial post.
Francis Brown wrote: 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" wrote: 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 -- Dave Peterson |
Copy Dates
Thanks for Input.
Working now. Francis "Dave Peterson" wrote: You have a reply at your initial post. Francis Brown wrote: 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" wrote: 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 -- Dave Peterson |
| All times are GMT +1. The time now is 05:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com