I did a fair bit of testing, including setting my system time zone to "Sydney". While I'm still formulating any real hard conclusion(s), the main problem is with what Excel recognizes as dates vs. a date in text format. Rather than putting in words, the following experiment will help give you clarity. In the GUI, format cell A1 (blank initially) as a date (dd/mm/yyyy format). Set B1 as =A1 and formatted as General. Now enter dates in textual format in A1, e.g. 30/1/<year>, and 1/30/<year>, 1/11/<year> and 11/1/<year>. When B2 yields a date serial number, such as 41473 for today, Excel recognizes the entry as a date. If B2 displays identical to A1, then it does not recognize A1 as a date and displays the entry as text, even though A1 is date formatted. In effect, you will learn what textual date entries Excel interprets as a date and how they get formatted.
The true point I'm attempting to make is that Excel processes date functions using only date serial numbers. If your entry is a value which is not interpretted as a date serial number, Excel will still process it, but as a text string.
As an experiment, fill down A1:B1 to A13:B13, then run this sub....
Sub test()
Dim j As Integer
Dim sDate As Date
sDate = DateValue(Range("A1").Value)
For j = 1 To 12
Range("A" & j + 1) = DateAdd("m", j, sDate)
Next j
End Sub
Make sure you run the sub with A1 values that Excel recognizes as dates and also ones it does not, as explained above. You'll see that even the entries not recognized as dates get processed... and errrantly, I might add. In short, you are getting errors because your DateAdd "date" argument is not being recognized as a date (i.e. date serial number) in certain instances. Whether it be the ones such as 12/11/2009, or 29/11/2009, I do not know for certain. Anyway, after determining what it takes to get the proper DateAdd "date" argument, if the output is in American format, you can add date formatting to your code. But date formatting makes no difference when the DateAdd "date" argument is not recognized as a date serial number.
Oh, BTW, here's another little experiment which will help provide clarity... Change the DateAdd "date" argument to #<textual_short_date>#, e.g. #30/11/2009#, then move your cursor to another line.
LinkBack URL
About LinkBacks
Register To Reply
below the post.
Re: VBA + Excel Date problem
). In A1, type 29/11/2009 (aka November 29, 2009). In A2, type 12/11/2009 (aka November 12, 2009). Put your formula in B1 and copy down to B2. What do you get for the results in B1 and B2?
Originally Posted by forfriends
Bookmarks