Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange date formatting problem
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
F2 shortcut and strange formatting of date fields | Excel Discussion (Misc queries) | |||
Date Formatting problem | Excel Discussion (Misc queries) | |||
Date Formatting Problem | Excel Discussion (Misc queries) | |||
Date formatting problem | Excel Discussion (Misc queries) | |||
Problem with Date Formatting | Excel Discussion (Misc queries) |