+ Reply to Thread
Results 1 to 31 of 31

VBA + Excel Date problem

  1. #1
    Registered User
    Join Date
    03-27-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    23

    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

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: VBA + Excel Date problem

    Hello forfriends,

    What is your goal? To have the function recognize both date formats?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: VBA + Excel Date problem

    Have you tried adding the months before concatenating?

  4. #4
    Registered User
    Join Date
    03-27-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    23

    Cool Re: VBA + Excel Date problem

    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.

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: VBA + Excel Date problem

    Hello forfriends,

    Let me ask you this, is this data imported from another file?

  6. #6
    Registered User
    Join Date
    03-27-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: VBA + Excel Date problem

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

  7. #7
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: VBA + Excel Date problem

    Hello forfriends,

    How do you decide if the date is to be in US format or not if all the dates are formatted the same?

  8. #8
    Registered User
    Join Date
    03-27-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: VBA + Excel Date problem

    I don't understand what you mean.

  9. #9
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: VBA + Excel Date problem

    Hello forfriends,

    Windows is set to interpret your date and time settings based on where you live. In Australia the dates are formatted as day, month year. If you were using Windows for the US it would default to month, day, year.

    Formatting only changes the display of the date, not how your system will interpret a date based on the rules for where you live.

    If all the dates are displayed in Australian format then how do you know which of those dates should be changed into US date format?

  10. #10
    Registered User
    Join Date
    03-27-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: VBA + Excel Date problem

    Answer:
    NONE of those dates should be treated as US format and my regional and language settings are dd/mm/yyyy.

    View:
    I believe you're misinterpreting the relevance US dates have here. US dates have NO relevance whatsoever EXCEPT for the fact that excel bugs out when it CAN (but shouldn't) be interpreted as one. Please refer to my quote again:

    "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."

    "DateAdd works correctly with strings so Table is correct but when placed in a cell it is misinterpreted."

    I apologise if I have not been clear enough about this.
    Last edited by forfriends; 07-16-2013 at 09:54 PM.

  11. #11
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: VBA + Excel Date problem

    Hello

    This statement is meaningless:
    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.
    Endian is a memory format referring to the position of the LSB in physical memory. Little Endian means the LSB is loaded first when memory is stored. Big Endian means the LSB is the last byte loaded into memory. There is no such thing as Middle Endian.

    I can 't help you you, if aren't clear about what you need.

  12. #12
    Registered User
    Join Date
    03-27-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: VBA + Excel Date problem

    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!

  13. #13
    Registered User
    Join Date
    03-27-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: VBA + Excel Date problem

    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 .

  14. #14
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: VBA + Excel Date problem

    Hello forfriends,

    If you system is set to recognize Australian date formats, which it is, entering a non Australian date format will generate an error. The same will happen on a US system. Any non US date format entered will generate an error. Did you expect some other result?

  15. #15
    Registered User
    Join Date
    03-27-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: VBA + Excel Date problem

    All dates are Australian dates. I am never entering any non-australian dates.

    The error occurs when my Australian dates can be interpreted as American dates, which happens when the day is less than or equal to the 12th, yet for when the date is unambiguously Australian (day is greater than 12) it works correctly.

    How about this?

    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

    I think you're failing to understand that some dates can be ambiguously Australian and American, and it is for these it screws up :S

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

    I want it to add to the month, as my code DateAdd("m", j, Day(DoD) & Run_Start) suggests.

    Expected output:
    12/12/2009
    12/1/2010
    12/2/2010
    12/3/2010
    Last edited by forfriends; 07-16-2013 at 10:39 PM.

  16. #16
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: VBA + Excel Date problem

    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...

  17. #17
    Registered User
    Join Date
    03-27-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: VBA + Excel Date problem

    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.

  18. #18
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: VBA + Excel Date problem

    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....

    Please Login or Register  to view this content.
    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.
    Last edited by jhren; 07-17-2013 at 04:56 PM.

  19. #19
    Registered User
    Join Date
    03-27-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: VBA + Excel Date problem

    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.

  20. #20
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: VBA + Excel Date problem

    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.

  21. #21
    Registered User
    Join Date
    03-27-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: VBA + Excel Date problem

    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.

  22. #22
    Registered User
    Join Date
    03-27-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: VBA + Excel Date problem

    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.
    Attached Images Attached Images

  23. #23
    Forum Contributor
    Join Date
    11-04-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    334

    Re: VBA + Excel Date problem

    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.

  24. #24
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: VBA + Excel Date problem

    Quote Originally Posted by forfriends View Post
    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...!!!

  25. #25
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: VBA + Excel Date problem

    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?

  26. #26
    Forum Contributor
    Join Date
    11-04-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    334

    Re: VBA + Excel Date problem

    jhren,

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

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

  27. #27
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: VBA + Excel Date problem

    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.
    Last edited by jhren; 07-18-2013 at 01:55 PM.

  28. #28
    Forum Contributor thameem127's Avatar
    Join Date
    04-06-2012
    Location
    Jeddah,Saudi Arabia
    MS-Off Ver
    Excel 2003,Excel 2007
    Posts
    321

    Re: VBA + Excel Date problem

    upload your file to avoid confusion

  29. #29
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: VBA + Excel Date problem

    Quote Originally Posted by jhren View Post
    ...

    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?
    Attached Files Attached Files

  30. #30
    Forum Contributor
    Join Date
    11-04-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    334

    Re: VBA + Excel Date problem

    Quote Originally Posted by jhren View Post
    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!

  31. #31
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: VBA + Excel Date problem

    Quote Originally Posted by anrichards22 View Post
    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...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel Date problem
    By Barryt0 in forum Excel General
    Replies: 1
    Last Post: 03-24-2009, 07:15 AM
  2. excel date problem
    By Biff in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-06-2005, 05:05 PM
  3. excel date problem
    By Saiju Mammen in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  4. excel date problem
    By Saiju Mammen in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  5. excel date problem
    By Saiju Mammen in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-15-2005, 09:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1