why is this causing a problem?

codesmith

Active Member
Joined
Apr 23, 2008
Messages
257
Code:
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.
 
Last edited:

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
L

Legacy 98055

Guest
I did not recieve any errors on my machine. Try this:

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

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,916
Its not clear what is a Date and what is a String
Code:
Dim nmiDate As String
Dim origDate As String
nmiDate = CDate(nmiDate)
origDate = CDate(origDate

Perhaps
Code:
Sub testDateCompareA()
    Dim nmiDate As String
    Dim origDate As String
    Dim minDate As String

    nmiDate = "23/4/08"
    origDate = "23/04/2008 15:05"
    
    minDate = CStr(CDate(WorksheetFunction.Min(Int(CDate(origDate)), Int(CDate(nmiDate)))))
    MsgBox minDate
End Sub

EDIT: Neither did I get the error. I suspect that an error comes when there is no time in origDate.
Which ends up eventualy erroring on Min(vbNullString).
Making sure that there is a space to be found fixes that. InStr(origDate & " ", " ")
 
Last edited:

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
What are you tring to do ?
Rich (BB code):
Sub testDateCompare()
    Dim nmiDate As Date,origDate As Date, minDate As Date
    nmiDate = CDate("23/4/08")
    origDate = CDate("23/04/2008 15:05")
    minDate = WorksheetFunction.Min(origDate, nmiDate)
    MsgBox minDate
End Sub
 

codesmith

Active Member
Joined
Apr 23, 2008
Messages
257

ADVERTISEMENT

jindon,

I have two date formats:

"23/4/08" & "23/04/2008 15:05"

I want to find the smallest date, ignoring the hhmm component.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
jindon,

I have two date formats:

"23/4/08" & "23/04/2008 15:05"

I want to find the smallest date, ignoring the hhmm component.
That's what I don't understand...
Why do you care about with/without time ?
try this anyway...
Rich (BB code):
Sub testDateCompare()
    Dim nmiDate As Date,origDate As Date, minDate As Date
    nmiDate = CDate("23/4/08")
    origDate = CDate("23/04/2008 15:05")
    minDate = CDate(WorksheetFunction.Min(CLng(origDate), CLng(nmiDate)))
    MsgBox minDate
End Sub
 
Last edited:

codesmith

Active Member
Joined
Apr 23, 2008
Messages
257

ADVERTISEMENT

because "23/4/08" might be considered: 23/4/08 00:00
and "23/04/2008 15:05" will be considered: 23/04/2008 15:05 (i.e. 15.05 hours after the first date).

I dont want to consider the time factor in my comparison ... just the date of day.


if you get me!
 

codesmith

Active Member
Joined
Apr 23, 2008
Messages
257
jindon,

result is: 21/04/08 !!?!?


Code:
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 minDate
End Sub
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,916
Jindon, the observation about the time component is astute.

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

Watch MrExcel Video

Forum statistics

Threads
1,123,053
Messages
5,599,524
Members
414,315
Latest member
Yolanda5050

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top