why is this causing a problem?

[codesmith]

Sub testDateCompare()
    Dim nmiDate As String
    nmiDate = CDate("23/4/08")
    Dim origDate As String
    origDate = CDate("23/04/2008 15:05")
    origDate = Left(origDate, InStr(origDate, " "))
    
    Dim minDate As String
    minDate = WorksheetFunction.Min(origDate, nmiDate)
    MsgBox minDate
End Sub

Giving '400' error!?

thanks in advance.

 

[Legacy 98055]

See the TimeValue and DateValue functions in VBA help...

 

[codesmith]

Jindon, the observation about the time component is astute.

result = Int(Min(a,b)) is more efficiant than result = Min(Int(a),Int(B))

earlier when I converted date to a long format, i was getting a floating point number ... this is why i was lead to believe i need to remove the time component.


and by the looks of it, with time 15.05 .. it was rounding up to next day ... this is why i was getting 21/04/08 as a result!!!

see below:

Sub testDateCompare()
    Dim nmiDate As Date, origDate As Date, minDate As Date
    nmiDate = CDate("23/4/08")
    origDate = CDate("20/04/2008 15:05")
    minDate = CDate(WorksheetFunction.Min(CLng(origDate), CLng(nmiDate)))
   
    MsgBox Format(CLng(nmiDate), "dd/mm/yyyy") 'result: 23/04/08
    MsgBox Format(CLng(origDate), "dd/mm/yyyy") 'result: 21/04/08
    
    origDate = CDate("20/04/2008 11:05") '##changed
    MsgBox Format(CLng(nmiDate), "dd/mm/yyyy") 'result: 23/04/08
    MsgBox Format(CLng(origDate), "dd/mm/yyyy") 'result: 20/04/08
    
End Sub

 

[mikerickson]

CLng doesn't truncate, it rounds. Int trucates.

 

[jindon]

Jindon, the observation about the time component is astute.

result = Int(Min(a,b)) is more efficiant than result = Min(Int(a),Int(B))

Read the op's code again.
He wants to compare the dates without time part. (doesn't mean much to me anyway though)

 

[codesmith]

Read the op's code again.
He wants to compare the dates without time part. (doesn't mean much to me anyway though)

hey man, why does it not mean much, obviously i am missing some crucial bit of information here

 

[jindon]

Then try

minDate = CDate(WorksheetFunction.Min(Fix(origDate), Fix(nmiDate)))

 

[codesmith]

as you can see:

Sub testDateCompare()
    Dim nmiDate As Date, origDate As Date, minDate As Date
    
    nmiDate = CDate("20/4/08")
    origDate = CDate("20/04/2008 15:05")
        
    MsgBox CLng(nmiDate) '39558
    MsgBox CLng(origDate) '39559
    
    
    
    origDate = Left(origDate, InStr(origDate, " "))
    MsgBox CLng(nmiDate) '39558
    MsgBox CLng(origDate) '39558
    
End Sub

 

[jindon]

hey man, why does it not mean much, obviously i am missing some crucial bit of information here

Because you are comparing DATE, which is Double type of data (Numeric).
What's the matter about returning "21/04/2008", when minimun of "21/04/2008" and "21/04/2008 07:20" ?

 

[codesmith]

Then try

minDate = CDate(WorksheetFunction.Min(Fix(origDate), Fix(nmiDate)))

beautiful mate!! cheers for your help man.

 

[codesmith]

Because you are comparing DATE, which is Double type of data (Numeric).
What's the matter about returning "21/04/2008", when minimun of "21/04/2008" and "21/04/2008 07:20" ?

there is a reason, if var holding 21/04/2008 07:20 is returned as a greater date than var holding just 21/4/08 ... then it tells me something which i dont want it to.

(it's because of the logic that surrounds this simple routine!!)