Datepart function return m = 12 for empty cells

I have been running to problem with datapart function while debugging existing macros. The problems is datepart returns 12 for m when it reads an empty cell. So the scripts get into a never ending loop of reading empty cells and returning 12; eventually program hangs.

Here is the simple script:

Please Login or Register  to view this content.

The data in column 2 of sheet1 is as follows:
11/01/2011
11/02/2011
11/03/2011
11/03/2011
12/1/2011
12/1/2011
12/2/2011
11/03/2011

Any suggestions why datepart does not do what it is supposed to do? Please advise.
Is this an application bug never reported?

Thanks,

Hi there.

I know it's a work around, but you could try adding an IF statement in your loop:

Please Login or Register  to view this content.

Best regards, Rob.

I'd set the For loop to only loop through cells with values

Please Login or Register  to view this content.

if empty is evaluated as zero, datepart will return the month number that refers to that date (in Excel dates are numbers): 31-12-1899
If you only want dates to be evaluated, please indicate that accordingly in the code:

Please Login or Register  to view this content.