Strange date formatting problem

  • Thread starter Thread starter RB Smissaert
  • Start date Start date
R

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
 
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
 
That is not the problem.
IntDate is always an integer number like this: yyyymmdd

RBS
 
Then try it like this:

Format(DateSerial(Right(intDate, 2),
Mid(intDate, 5, 2), _
Left(intDate, 4)), "dd/mm/yyyy")
 
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
 
Back
Top