VBA + Excel Date problem

Hi guys,

New to macros here.

Have a macro that does a reasonably simple calculation:

Table(j, 13) = DateAdd("m", j, Day(DoD) & Run_Start)

Basically pulls the day from a another date called DoD, concatenates it to the month and year, and adds a few months onto it.

Now, for dates which cannot be interpreted in the american date format, this works correctly.

Output example:
DoD (in)
29/11/2009
29/11/2009
29/11/2009
29/11/2009
29/11/2009
29/11/2009

Table (out)
29/12/2009
29/01/2010
28/02/2010
29/03/2010
29/04/2010
29/05/2010

Good!

For dates which can be valid american dates, it adds to the day:
DoD (in)
12/11/2009
12/11/2009
12/11/2009
12/11/2009

Table (out)
1/11/2010
2/11/2010
3/11/2010
4/11/2010

Bad..

Does anyone know how to fix this?

I try to do my research before posting and have tried to solve it by putting ReDim ClaimsTable(j, 13) As Date in the loop, but it doesn't seem to like it.

Thanks

Have you tried adding the months before concatenating?

All DoD dates are in dd/mm/yyyy.

For DoD dates that can be interpreted as American date format (dd<=12), the function works incorrectly as decribed above, producing incorrect output which adds to the day rather than the month.

For DoD dates that cannot be interpreted as an American date formate, the function works correctly.

My goal is to rectify this error.

http://stackoverflow.com/questions/9...-automatically

"Major issue

You have hit the Excel bug that it will interpret dates as being in American (middle endian) format if it can when transferring data to a worksheet."

This is also where I got my attempted solution from but I don't know how to apply it correctly despite trying it several times (I'm inexperienced).

Also tried http://www.mrexcel.com/forum/excel-q...e-problem.html.

It gets taken from another worksheet, in which all dates are formatted as dd/mm/yyyy.

I don't understand what you mean.

I think I am being exceedingly clear about what I need.

I have a function in VBA which writes dates to Excel.

Excel misinterprets the output for particular dates which happen to be dates in which dd<=12.

Simple!

Is my first post not clear enough? Purpose: Add a few months onto a date.

For DoD with dd>12, the output is good, with the months increasing sequentually, hence the Good!

For DoD with dd<=12, the output is bad, with the DAYS increasing sequentually and the month remaining constant, hence the Bad .

Sounds a lot like an application bug. If your code works on dates which are certainly not US date format, and does not on dates which can be interpretted as US dates, how do you expect us to resolve it??? I know I certainly cannot.

The only option as I see it is to try some kind of workaround. If you persist on sticking with your current code, the best I think anyone on this forum, including me, can do is wish you the best of luck...

Thanks for your response.

It appears so. However, in the two links I have provided, apparently solutions exist, but I suppose my VBA knowledge isn't good enough to understand and adapt their code to my situation, if I am correct in that those solutions do apply in the first place.

What you are saying makes sense.

DoD dates in the input worksheet are indeed correct serial numbers. This is copied to the output worksheet using
Table(4, j) = DoD

Now, this has the same problem with Table(13, j) = Dateadd.

If dd<=12:
A serial number is outputted, formatted as a UK date. However, the day and month is swapped - i.e. it would be correct if one were to interpret it in 'real life' as a US date.

If dd>12
Text is outputted, formatted as text. It is correct in 'real life' if one were to print it on a piece of paper and read it.

Essentially, I have an output which is a mish-mash of (correct) text and (incorrect, but can be made to be correct by swapping the month and the day) serial numbers.

Whilst my excel skills are good enough to do if(isvalue, date(year,day,month)) or something similar, as this is a 'button-press' operation, I would like it to have an output either of all correct serial numbers (and let excel handle the formatting via regional and language settings) or of all correct text from the push of the "run" button only.

Can you post a sample file, with code?

Without having actual values and the code to see what's happening, everything from this end is just a shot in the dark.

Yes, it is definitely my aim to get something like that for you guys. Thanks for your responses btw - very helpful.

I'll post back here/edit this post when I have a spare second to put something together.

Please see screenshot - the best I can do.

Please note the reversed DOB and DOD from the input to the output if dd<=12. While my initial query was about column N (investigation date) it is also affecting every other date in the output spreadsheet.

fofriends,

I had the same issue as you not so long ago. Dates and VBA code are a nightmare.
Your computer regional settings are supposed to overcome this problem but from my experience it most certainly DOES NOT!!


Assuming the cell A1 contains the data that you want to add 1 month to, this formula will add one month:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
See if you can incorporate the formula into your code.

Originally Posted by forfriends

Please see screenshot - the best I can do.

Please note the reversed DOB and DOD from the input to the output if dd<=12. While my initial query was about column N (investigation date) it is also affecting every other date in the output spreadsheet.

As I said, in short, we need an Excel file... with cells that contain the actual dates as used. And we need more of the code...

We (I) don't want you to post any sensitive information. You can delete all but the dates which are affected, and we only need a small cross section of those. Seeing all relevant code would help also. You are giving us a few lines, and we can glean a fair amount from that, but for the code presented, we also need to see how all the pertinent variables are dim'd or otherwise set. For example, the DateAdd "date" argument uses >DoD< and >Run_Start<. On our end, those mean exactly nothing because we don't know how their values or parameters are established.

They may say a picture is worth a thousand words... but it is hardly a substitute for the real deal...!!!

anr,

While all suggestions are appreciated, I don't believe that will help.

Here's a little experiment for you to try with your formula. With a totally new blank sheet, format cells A1:B2 to dd/mm/yyyy any way you please (your being from Australia should make that relatively painless ). 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?

jhren,

B1 = 29/12/2009 and B2 = 12/12/2009

The way vba screws up dates is un-Australian..forfriends will get the joke!

upload your file to avoid confusion

Originally Posted by jhren

...

IMO, though I have not really attempted it, is to VBA process the textual dates and replace them with their date serial numbers (the cell format shouldn't have to change). This should result in correct dates no matter who (or should I say where?) opens the file, though the user may have to change cell format.


PS: Here's another interesting tidbit on the bug. While with d/M/yyyy system setting, I change the format of A1:B2 to General, all but A1 are converted to date serial number. A1 remains 29/11/2009.

Follow up...

I created a file (attached) to test my VBA theory, and AFAICT, I was correct. The code places the serial date (sdate) in column B for comparison, but just as easily could be placed back in column A, replacing the orginal textual date entries. The thing to note is columns C and D, which represent what Excel stores for the respective cells of A and B... while columns E and F show that Excel can properly interpret what Excel stores as long as it is "told" the cell value is a date and its format. Column H is the result of DateAdd of 3 months to column B (sdate). They all appear to be correct.

On a side note, I noticed Excel automatically formatted column H as "*14/3/2001" and English (U.S.) locale. I think this is because my system language is still set as the latter. The * in front of the date format means it will change according to regional system settings. So here's a question that I now have... is there a way to set this "*" date format through VBA?

Originally Posted by jhren

anr,

Here's the thing about the experiment I asked you to do...

When I have my system Regional Options set to display short date in d/M/yyyy format, I get the same result as you...

B1 = 29/12/2009 and B2 = 12/12/2009

However, if I have my system set for M/d/yyyy, I get...

B1 = #VALUE! and B2 = 11/1/2009

In both cases, the cell date format is "14/3/2001" and English (Australian) locale.

What this means, at least from a GUI perspective, is that a file with dates entered under Australian regional system settings is opened by a user with U.S. regional system settings, Excel interprets the values as entered. As such, 29/11/2009 is interpretted as text (thus the #VALUE! error in B1), while 12/11/2009 is interpretted as a date of 11/12/2009, which is correct for a U.S. user, but not when cell format is dd/mm/yyy... and then it is further processed by calculation, e.g. adding 1 month in B2, as 11/1/2009.

As I noted earlier, it is an application bug.

IMO, though I have not really attempted it, is to VBA process the textual dates and replace them with their date serial numbers (the cell format shouldn't have to change). This should result in correct dates no matter who (or should I say where?) opens the file, though the user may have to change cell format.


PS: Here's another interesting tidbit on the bug. While with d/M/yyyy system setting, I change the format of A1:B2 to General, all but A1 are converted to date serial number. A1 remains 29/11/2009.

Jhren,

I am aware of the bug because I have experienced the problem first hand.

As forfriends has stated, the columns are formatted as dd/mm/yyyy as is our custom in this part of the world.
It is my experience that the 'bug' appears when data is copied and manipulated through VBA.
The formula that I suggested has worked in one of my VBA projects to overcome the bug. Why or how I do not know!

Originally Posted by anrichards22

Jhren,

I am aware of the bug because I have experienced the problem first hand.

As forfriends has stated, the columns are formatted as dd/mm/yyyy as is our custom in this part of the world.
It is my experience that the 'bug' appears when data is copied and manipulated through VBA.
The formula that I suggested has worked in one of my VBA projects to overcome the bug. Why or how I do not know!

And that formula may work fine for forfriends. While my reply was directed to you in particular, it was implicitly directed at all readers, including forfriends, who had mentioned a preference for letting system settings taking care of any formatting issues. My point was simply that your formula is not an all encompassing workaround. The base means of a true workaround is in my follow up, represented by column H. What's left is adapting that to the particular file in question...