Strange date formatting problem

[RB Smissaert]

Using Excel 2000 to 2003.
I have to compare columns of dates in a VBA array. This array is delared as
follows:
Public TableArray()
The dates are entered via another variant array with a function that goes as
follows:

Function ConvertDates(ByVal intDate As Long) As String

If intDate = 0 Or intDate = Null Then
ConvertDates = ""
Else
ConvertDates = _
Format(DateSerial(Left(intDate, 4), _
Mid(intDate, 5, 2), _
Right(intDate, 2)), "dd/mm/yyyy")
End If

End Function

Now I have to compare dates in 2 colums of the TableArray.
I can't get this to work and I found out that this is because the dates are
stored in the array in the
"mm/dd/yyyy" format. Strangely when you assign the array to the sheet it
appears fine as "dd/mm/yyyy".
Now I have tried all kind of ways to compare the dates in the 2 columns, but
nil works. This is because I have to
do the date comparisons in proper time units like a real calendar month,
rather than 30 days.
This goes via another function like this:

(snippet)
AlterDate = DateSerial(Year(oldDate), _
Month(oldDate) - lCount, _
Day(oldDate))

The month and day being the other way round messes this function up.

Would there be any solution to this rather than swapping the day and month
via a temp variable?

Thanks for any advice.



RBS

 

[pikus]

Your problem most likely comes from the fact that this code will only
work if intDate has the correct number of digits. Something as simple
as Jan – Sept being given as “1” – “9” instead of “01” – “09” could
cause it to malfunction. - Pikus

 

[RB Smissaert]

That is not the problem.
IntDate is always an integer number like this: yyyymmdd

RBS

 

[pikus]

Then try it like this:

Format(DateSerial(Right(intDate, 2),
Mid(intDate, 5, 2), _
Left(intDate, 4)), "dd/mm/yyyy")

 

[RB Smissaert]

That is what I am doing already.

RBS

 

[pikus]

Did you note the change of order? - Pikus

 

[RB Smissaert]

OK, I noticed now, but that just gives me a wrong date altogether.
The function to convert to a date from the integer number yyyymmdd is fine.
This is really puzzeling.

RBS