[Solved] VBA: XP date issue

  • Hi All

    I'm in Oz so our dates are dd/mm/yy.

    I've got an XP issue. The following piece of code works correctly in 2000

    .Offset(CurrRow, 31).Value = Format(CurrAdjFinishDate, "dd-mm-yy h:mm")
    and produces
    13-11-03 0:03

    However when run on an XP machine with XL XP it produces the following

    11/13/2002 12:03:00 AM

    It thinks the 13 is the month not the day even though the above code ensures the format of the date is correct. Iv'e checked the formatthrough a msgbox and it looks OK - it only when it is put in the cell that it goes haywire.

    Changing the format of the cell itself has no effect on the contents. It thinks it text because you can't have a 13th month.

    The system dates are correct and use English(Australian).

    Any ideas????

    There are three types of people in this world.
    Those who can count and those who can't.

  • Hi Neale,

    The Format statement does return a String (not a date value) as you said.

    I pasted your line of code into a macro in my XL XP setup and it seemed to work OK. The result you are getting (11/13/2002 12:03:00 AM) is, of course, no where close to the formatting you specify in the statement - - I assume the 2002 date was a typo - the formula didn't change the year on you, did it? It looks like for some reason the macro is not even looking at that part of the statement.

    Try taking the format code out of the stmt and see what you get i.e. =Format(CurrAdjFinishDate)
    or try some of the system format codes
    =Format(CurrAdjFinishDate, "Long Date")


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!