| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA dates versus Excel dates
There is a strange in the inconsistency between VBA dates and Excel dates.
If you execute the following VBA code: Sub test() Dim d As Date, dd As Date, dr As Date, r As Range, i As Integer d = " 14 aug 2008 23:00" dd = "00:05" Set r = Range("A1") For i = 1 To 16 d = d + dd r.Offset(i) = d 'store it in a cell dr = r.Offset(i) 'read the value back Debug.Print d, dr Next End Sub the result is: 14/08/2008 23:05:00 14/08/2008 23:05:00 14/08/2008 23:10:00 14/08/2008 23:10:00 14/08/2008 23:15:00 14/08/2008 23:15:00 14/08/2008 23:20:00 14/08/2008 23:20:00 14/08/2008 23:25:00 14/08/2008 23:25:00 14/08/2008 23:30:00 14/08/2008 23:30:00 14/08/2008 23:35:00 14/08/2008 23:35:00 14/08/2008 23:40:00 14/08/2008 23:40:00 14/08/2008 23:45:00 14/08/2008 23:45:00 14/08/2008 23:50:00 14/08/2008 23:50:00 14/08/2008 23:55:00 14/08/2008 23:55:00 15/08/2008 14/08/2008 15/08/2008 00:05:00 15/08/2008 00:05:00 15/08/2008 00:10:00 15/08/2008 00:10:00 15/08/2008 00:15:00 15/08/2008 00:15:00 15/08/2008 00:20:00 15/08/2008 00:20:00 It is seen that a incompatibility occurs "at midnight": When you store the VBA date "15/08/2008 00:00" in a cell in Excel, then it is interpreted as "14/08/2008 00:00". And when the cell is read back into a VBA date variable, then it is interpreted by VBA as "14/08/2008 00:00". I reported the error in Excel2003, but aparently it has survived into Excel2007. Is'nt it time to remove this very confusing error (which, depending on the circumstances, can be very serious) ? Regards Morten ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...el.programming |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA dates versus Excel dates
Troublesome. I reproduced it in 2003 and 2007. Then I tried a couple things.
1. I used the .Value property of the range objects you were writing to and reading from, rather than rely on the default (which should be .Value, but whatever). I also put the dates within hashes rather than double quotes. The problem persisted. 2. I did my calculations with doubles. The correct values were saved in the worksheet. 3. I used the .Value2 property when writing the dates to the worksheet. The problem went away in 2003 and in 2007. I'll submit the problem as a bug, but in the meantime use the .Value2 property of the range, as in my example below. Sub test2() Dim dt1 As Date, dt0 As Date, dt2 As Date, dt3 As Date Dim r As Range, i As Integer Dim db0 As Double, db1 As Double, db2 As Double dt1 = #8/14/2008 11:00:00 PM# dt0 = #12:05:00 AM# db1 = CDbl(dt1) db0 = CDbl(dt0) Set r = Range("A1") For i = 1 To 16 dt1 = dt1 + dt0 db1 = db1 + db0 r.Offset(i).Value = dt1 'store it in a cell r.Offset(i, 1).Value = db1 r.Offset(i, 2).Value2 = dt1 dt2 = r.Offset(i).Value 'read the value back db2 = r.Offset(i, 1).Value dt3 = r.Offset(i, 1).Value Debug.Print dt1, dt2, dt3, CDate(db1), CDate(db2) Next End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Morten Hvidberg-Knudsen" wrote in message ... There is a strange in the inconsistency between VBA dates and Excel dates. If you execute the following VBA code: Sub test() Dim d As Date, dd As Date, dr As Date, r As Range, i As Integer d = " 14 aug 2008 23:00" dd = "00:05" Set r = Range("A1") For i = 1 To 16 d = d + dd r.Offset(i) = d 'store it in a cell dr = r.Offset(i) 'read the value back Debug.Print d, dr Next End Sub the result is: 14/08/2008 23:05:00 14/08/2008 23:05:00 14/08/2008 23:10:00 14/08/2008 23:10:00 14/08/2008 23:15:00 14/08/2008 23:15:00 14/08/2008 23:20:00 14/08/2008 23:20:00 14/08/2008 23:25:00 14/08/2008 23:25:00 14/08/2008 23:30:00 14/08/2008 23:30:00 14/08/2008 23:35:00 14/08/2008 23:35:00 14/08/2008 23:40:00 14/08/2008 23:40:00 14/08/2008 23:45:00 14/08/2008 23:45:00 14/08/2008 23:50:00 14/08/2008 23:50:00 14/08/2008 23:55:00 14/08/2008 23:55:00 15/08/2008 14/08/2008 15/08/2008 00:05:00 15/08/2008 00:05:00 15/08/2008 00:10:00 15/08/2008 00:10:00 15/08/2008 00:15:00 15/08/2008 00:15:00 15/08/2008 00:20:00 15/08/2008 00:20:00 It is seen that a incompatibility occurs "at midnight": When you store the VBA date "15/08/2008 00:00" in a cell in Excel, then it is interpreted as "14/08/2008 00:00". And when the cell is read back into a VBA date variable, then it is interpreted by VBA as "14/08/2008 00:00". I reported the error in Excel2003, but aparently it has survived into Excel2007. Is'nt it time to remove this very confusing error (which, depending on the circumstances, can be very serious) ? Regards Morten ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...el.programming |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA dates versus Excel dates
Thanks
Never heard of the ".value2" property before, but aparently now is the time to study it. Morten "Jon Peltier" wrote: Troublesome. I reproduced it in 2003 and 2007. Then I tried a couple things. 1. I used the .Value property of the range objects you were writing to and reading from, rather than rely on the default (which should be .Value, but whatever). I also put the dates within hashes rather than double quotes. The problem persisted. 2. I did my calculations with doubles. The correct values were saved in the worksheet. 3. I used the .Value2 property when writing the dates to the worksheet. The problem went away in 2003 and in 2007. I'll submit the problem as a bug, but in the meantime use the .Value2 property of the range, as in my example below. Sub test2() Dim dt1 As Date, dt0 As Date, dt2 As Date, dt3 As Date Dim r As Range, i As Integer Dim db0 As Double, db1 As Double, db2 As Double dt1 = #8/14/2008 11:00:00 PM# dt0 = #12:05:00 AM# db1 = CDbl(dt1) db0 = CDbl(dt0) Set r = Range("A1") For i = 1 To 16 dt1 = dt1 + dt0 db1 = db1 + db0 r.Offset(i).Value = dt1 'store it in a cell r.Offset(i, 1).Value = db1 r.Offset(i, 2).Value2 = dt1 dt2 = r.Offset(i).Value 'read the value back db2 = r.Offset(i, 1).Value dt3 = r.Offset(i, 1).Value Debug.Print dt1, dt2, dt3, CDate(db1), CDate(db2) Next End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Morten Hvidberg-Knudsen" wrote in message ... There is a strange in the inconsistency between VBA dates and Excel dates. If you execute the following VBA code: Sub test() Dim d As Date, dd As Date, dr As Date, r As Range, i As Integer d = " 14 aug 2008 23:00" dd = "00:05" Set r = Range("A1") For i = 1 To 16 d = d + dd r.Offset(i) = d 'store it in a cell dr = r.Offset(i) 'read the value back Debug.Print d, dr Next End Sub the result is: 14/08/2008 23:05:00 14/08/2008 23:05:00 14/08/2008 23:10:00 14/08/2008 23:10:00 14/08/2008 23:15:00 14/08/2008 23:15:00 14/08/2008 23:20:00 14/08/2008 23:20:00 14/08/2008 23:25:00 14/08/2008 23:25:00 14/08/2008 23:30:00 14/08/2008 23:30:00 14/08/2008 23:35:00 14/08/2008 23:35:00 14/08/2008 23:40:00 14/08/2008 23:40:00 14/08/2008 23:45:00 14/08/2008 23:45:00 14/08/2008 23:50:00 14/08/2008 23:50:00 14/08/2008 23:55:00 14/08/2008 23:55:00 15/08/2008 14/08/2008 15/08/2008 00:05:00 15/08/2008 00:05:00 15/08/2008 00:10:00 15/08/2008 00:10:00 15/08/2008 00:15:00 15/08/2008 00:15:00 15/08/2008 00:20:00 15/08/2008 00:20:00 It is seen that a incompatibility occurs "at midnight": When you store the VBA date "15/08/2008 00:00" in a cell in Excel, then it is interpreted as "14/08/2008 00:00". And when the cell is read back into a VBA date variable, then it is interpreted by VBA as "14/08/2008 00:00". I reported the error in Excel2003, but aparently it has survived into Excel2007. Is'nt it time to remove this very confusing error (which, depending on the circumstances, can be very serious) ? Regards Morten ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...el.programming |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA dates versus Excel dates
I've hardly ever used it, but I know it can improve precision of data
transferred between worksheet and VBA. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Morten Hvidberg-Knudsen" wrote in message ... Thanks Never heard of the ".value2" property before, but aparently now is the time to study it. Morten "Jon Peltier" wrote: Troublesome. I reproduced it in 2003 and 2007. Then I tried a couple things. 1. I used the .Value property of the range objects you were writing to and reading from, rather than rely on the default (which should be .Value, but whatever). I also put the dates within hashes rather than double quotes. The problem persisted. 2. I did my calculations with doubles. The correct values were saved in the worksheet. 3. I used the .Value2 property when writing the dates to the worksheet. The problem went away in 2003 and in 2007. I'll submit the problem as a bug, but in the meantime use the .Value2 property of the range, as in my example below. Sub test2() Dim dt1 As Date, dt0 As Date, dt2 As Date, dt3 As Date Dim r As Range, i As Integer Dim db0 As Double, db1 As Double, db2 As Double dt1 = #8/14/2008 11:00:00 PM# dt0 = #12:05:00 AM# db1 = CDbl(dt1) db0 = CDbl(dt0) Set r = Range("A1") For i = 1 To 16 dt1 = dt1 + dt0 db1 = db1 + db0 r.Offset(i).Value = dt1 'store it in a cell r.Offset(i, 1).Value = db1 r.Offset(i, 2).Value2 = dt1 dt2 = r.Offset(i).Value 'read the value back db2 = r.Offset(i, 1).Value dt3 = r.Offset(i, 1).Value Debug.Print dt1, dt2, dt3, CDate(db1), CDate(db2) Next End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Morten Hvidberg-Knudsen" wrote in message ... There is a strange in the inconsistency between VBA dates and Excel dates. If you execute the following VBA code: Sub test() Dim d As Date, dd As Date, dr As Date, r As Range, i As Integer d = " 14 aug 2008 23:00" dd = "00:05" Set r = Range("A1") For i = 1 To 16 d = d + dd r.Offset(i) = d 'store it in a cell dr = r.Offset(i) 'read the value back Debug.Print d, dr Next End Sub the result is: 14/08/2008 23:05:00 14/08/2008 23:05:00 14/08/2008 23:10:00 14/08/2008 23:10:00 14/08/2008 23:15:00 14/08/2008 23:15:00 14/08/2008 23:20:00 14/08/2008 23:20:00 14/08/2008 23:25:00 14/08/2008 23:25:00 14/08/2008 23:30:00 14/08/2008 23:30:00 14/08/2008 23:35:00 14/08/2008 23:35:00 14/08/2008 23:40:00 14/08/2008 23:40:00 14/08/2008 23:45:00 14/08/2008 23:45:00 14/08/2008 23:50:00 14/08/2008 23:50:00 14/08/2008 23:55:00 14/08/2008 23:55:00 15/08/2008 14/08/2008 15/08/2008 00:05:00 15/08/2008 00:05:00 15/08/2008 00:10:00 15/08/2008 00:10:00 15/08/2008 00:15:00 15/08/2008 00:15:00 15/08/2008 00:20:00 15/08/2008 00:20:00 It is seen that a incompatibility occurs "at midnight": When you store the VBA date "15/08/2008 00:00" in a cell in Excel, then it is interpreted as "14/08/2008 00:00". And when the cell is read back into a VBA date variable, then it is interpreted by VBA as "14/08/2008 00:00". I reported the error in Excel2003, but aparently it has survived into Excel2007. Is'nt it time to remove this very confusing error (which, depending on the circumstances, can be very serious) ? Regards Morten ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...el.programming |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA dates versus Excel dates
Hi Jon,
I seem to remember reading that .value2 was the best option for currency and dates. I don't remember why. Barb Reinhardt "Jon Peltier" wrote: I've hardly ever used it, but I know it can improve precision of data transferred between worksheet and VBA. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Morten Hvidberg-Knudsen" wrote in message ... Thanks Never heard of the ".value2" property before, but aparently now is the time to study it. Morten "Jon Peltier" wrote: Troublesome. I reproduced it in 2003 and 2007. Then I tried a couple things. 1. I used the .Value property of the range objects you were writing to and reading from, rather than rely on the default (which should be .Value, but whatever). I also put the dates within hashes rather than double quotes. The problem persisted. 2. I did my calculations with doubles. The correct values were saved in the worksheet. 3. I used the .Value2 property when writing the dates to the worksheet. The problem went away in 2003 and in 2007. I'll submit the problem as a bug, but in the meantime use the .Value2 property of the range, as in my example below. Sub test2() Dim dt1 As Date, dt0 As Date, dt2 As Date, dt3 As Date Dim r As Range, i As Integer Dim db0 As Double, db1 As Double, db2 As Double dt1 = #8/14/2008 11:00:00 PM# dt0 = #12:05:00 AM# db1 = CDbl(dt1) db0 = CDbl(dt0) Set r = Range("A1") For i = 1 To 16 dt1 = dt1 + dt0 db1 = db1 + db0 r.Offset(i).Value = dt1 'store it in a cell r.Offset(i, 1).Value = db1 r.Offset(i, 2).Value2 = dt1 dt2 = r.Offset(i).Value 'read the value back db2 = r.Offset(i, 1).Value dt3 = r.Offset(i, 1).Value Debug.Print dt1, dt2, dt3, CDate(db1), CDate(db2) Next End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Morten Hvidberg-Knudsen" wrote in message ... There is a strange in the inconsistency between VBA dates and Excel dates. If you execute the following VBA code: Sub test() Dim d As Date, dd As Date, dr As Date, r As Range, i As Integer d = " 14 aug 2008 23:00" dd = "00:05" Set r = Range("A1") For i = 1 To 16 d = d + dd r.Offset(i) = d 'store it in a cell dr = r.Offset(i) 'read the value back Debug.Print d, dr Next End Sub the result is: 14/08/2008 23:05:00 14/08/2008 23:05:00 14/08/2008 23:10:00 14/08/2008 23:10:00 14/08/2008 23:15:00 14/08/2008 23:15:00 14/08/2008 23:20:00 14/08/2008 23:20:00 14/08/2008 23:25:00 14/08/2008 23:25:00 14/08/2008 23:30:00 14/08/2008 23:30:00 14/08/2008 23:35:00 14/08/2008 23:35:00 14/08/2008 23:40:00 14/08/2008 23:40:00 14/08/2008 23:45:00 14/08/2008 23:45:00 14/08/2008 23:50:00 14/08/2008 23:50:00 14/08/2008 23:55:00 14/08/2008 23:55:00 15/08/2008 14/08/2008 15/08/2008 00:05:00 15/08/2008 00:05:00 15/08/2008 00:10:00 15/08/2008 00:10:00 15/08/2008 00:15:00 15/08/2008 00:15:00 15/08/2008 00:20:00 15/08/2008 00:20:00 It is seen that a incompatibility occurs "at midnight": When you store the VBA date "15/08/2008 00:00" in a cell in Excel, then it is interpreted as "14/08/2008 00:00". And when the cell is read back into a VBA date variable, then it is interpreted by VBA as "14/08/2008 00:00". I reported the error in Excel2003, but aparently it has survived into Excel2007. Is'nt it time to remove this very confusing error (which, depending on the circumstances, can be very serious) ? Regards Morten ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...el.programming |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA dates versus Excel dates
It's in VBA's help???
<vbg Barb Reinhardt wrote: Hi Jon, I seem to remember reading that .value2 was the best option for currency and dates. I don't remember why. Barb Reinhardt "Jon Peltier" wrote: I've hardly ever used it, but I know it can improve precision of data transferred between worksheet and VBA. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Morten Hvidberg-Knudsen" wrote in message ... Thanks Never heard of the ".value2" property before, but aparently now is the time to study it. Morten "Jon Peltier" wrote: Troublesome. I reproduced it in 2003 and 2007. Then I tried a couple things. 1. I used the .Value property of the range objects you were writing to and reading from, rather than rely on the default (which should be .Value, but whatever). I also put the dates within hashes rather than double quotes. The problem persisted. 2. I did my calculations with doubles. The correct values were saved in the worksheet. 3. I used the .Value2 property when writing the dates to the worksheet. The problem went away in 2003 and in 2007. I'll submit the problem as a bug, but in the meantime use the .Value2 property of the range, as in my example below. Sub test2() Dim dt1 As Date, dt0 As Date, dt2 As Date, dt3 As Date Dim r As Range, i As Integer Dim db0 As Double, db1 As Double, db2 As Double dt1 = #8/14/2008 11:00:00 PM# dt0 = #12:05:00 AM# db1 = CDbl(dt1) db0 = CDbl(dt0) Set r = Range("A1") For i = 1 To 16 dt1 = dt1 + dt0 db1 = db1 + db0 r.Offset(i).Value = dt1 'store it in a cell r.Offset(i, 1).Value = db1 r.Offset(i, 2).Value2 = dt1 dt2 = r.Offset(i).Value 'read the value back db2 = r.Offset(i, 1).Value dt3 = r.Offset(i, 1).Value Debug.Print dt1, dt2, dt3, CDate(db1), CDate(db2) Next End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Morten Hvidberg-Knudsen" wrote in message ... There is a strange in the inconsistency between VBA dates and Excel dates. If you execute the following VBA code: Sub test() Dim d As Date, dd As Date, dr As Date, r As Range, i As Integer d = " 14 aug 2008 23:00" dd = "00:05" Set r = Range("A1") For i = 1 To 16 d = d + dd r.Offset(i) = d 'store it in a cell dr = r.Offset(i) 'read the value back Debug.Print d, dr Next End Sub the result is: 14/08/2008 23:05:00 14/08/2008 23:05:00 14/08/2008 23:10:00 14/08/2008 23:10:00 14/08/2008 23:15:00 14/08/2008 23:15:00 14/08/2008 23:20:00 14/08/2008 23:20:00 14/08/2008 23:25:00 14/08/2008 23:25:00 14/08/2008 23:30:00 14/08/2008 23:30:00 14/08/2008 23:35:00 14/08/2008 23:35:00 14/08/2008 23:40:00 14/08/2008 23:40:00 14/08/2008 23:45:00 14/08/2008 23:45:00 14/08/2008 23:50:00 14/08/2008 23:50:00 14/08/2008 23:55:00 14/08/2008 23:55:00 15/08/2008 14/08/2008 15/08/2008 00:05:00 15/08/2008 00:05:00 15/08/2008 00:10:00 15/08/2008 00:10:00 15/08/2008 00:15:00 15/08/2008 00:15:00 15/08/2008 00:20:00 15/08/2008 00:20:00 It is seen that a incompatibility occurs "at midnight": When you store the VBA date "15/08/2008 00:00" in a cell in Excel, then it is interpreted as "14/08/2008 00:00". And when the cell is read back into a VBA date variable, then it is interpreted by VBA as "14/08/2008 00:00". I reported the error in Excel2003, but aparently it has survived into Excel2007. Is'nt it time to remove this very confusing error (which, depending on the circumstances, can be very serious) ? Regards Morten ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...el.programming -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Linking computer dates (time) to spreadsheet dates that have formu | Excel Worksheet Functions | |||
| Excel not recognizing dates as dates | Excel Discussion (Misc queries) | |||
| Toggle a range of Julian dates to Gregorian Dates and Back | Excel Programming | |||
| need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
| How do I get the dates on an excel chart to stay as dates instead. | Charts and Charting in Excel | |||
Hybrid Mode
