VBA ISDATE for typos

  • Hi All,

    I'm having some trouble with ISDATE function. Basically I am importing data from word in such a format "30/01/2016 (04)" for instance to represent date and week. I want to get this simply as 30/01/2016 which I did, but its not consistent due to typos. So I used ISDATE, as well as checking there is a space and two brackets in text, and that the valued contrived is a date (basically mix of mid text and date value). As the vba code is amending dates I then ran a check to say if the new date is more than 125 days from old, it will not update and comment as such, before moving to next row. Youd think that would cover all bases and work right...

    Wrong! So Mrs Fat Thumbs managed to get "30/01/0216" as the date. Now the problem is ISDATE recognises this as a valid date (ok fine), but when I go to calculate ABS(30/01/0216 - 30/01/2016) > 125 it now comes up as an error. Would I be better off just using err.number and resume next instead? It seems a bit daft that the code recognises it as a date, then when treating it as date it does not.

    Prompt help is appreciated!

Participate now!

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