Strange date formatting problem
 

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

Strange date formatting problem
 

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


---
Message posted from http://www.ExcelForum.com/

Strange date formatting problem
 

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

RBS

"pikus " wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/

Strange date formatting problem
 

Then try it like this:

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


---
Message posted from http://www.ExcelForum.com/

Strange date formatting problem
 

That is what I am doing already.

RBS


"pikus " wrote in message
...
Then try it like this:

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


---
Message posted from http://www.ExcelForum.com/

Strange date formatting problem
 

Did you note the change of order? - Pikus


---
Message posted from http://www.ExcelForum.com/

Strange date formatting problem
 

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

"pikus " wrote in message
...
Did you note the change of order? - Pikus


---
Message posted from http://www.ExcelForum.com/