Comparing Date/Time

[alecbowman]

I have a user form which is used to specify an XY scatter plot where one of the axis is Date/Time.

This fails if the user inadvertently enters a Minimum Date/Time which is later than the Max Date/Time.

The obvious check to make is that the Min Date/Time < Max Date/Time but it appears as though excel can only treat entries from dialog boxes at text so this sort of comparison doesn't work.

Any suggestions would be gratefully received

Alec

 

[DominicB]

Good morning alecbowman

Excel can check the validity of dates, it's all a matter of specifying format (so Excel can be sure it's a date - after all we're using TextBoxes - could be anything). You haven't posted any code, so I can't tailor this example to your precise needs, but this example works on UserForm1, with the controls TextBox1, TextBox2 and CommandButton1 set up :

Private Sub CommandButton1_Click()
If Format(TextBox1, "dd/mmm/yy") < Format(TextBox2, "dd/mmm/yy") Then
MsgBox "You can't fo this!"
Exit Sub
Else
MsgBox "OK"
Unload UserForm1
End If
End Sub

HTH

DominicB

 

[alecbowman]

Thanks,

Haven't tried it yet but will let you know. I assume that this will work for any custom date/time formats as well (I am using a format "dd/mm/yyyy hh/mm/ss.000"!)

Alec

 

[DominicB]

Hi alecbowman

I assume that this will work for any custom date/time formats as well

Sure will - pretty much any format is OK really if you want to capture users input for %age, time, decimal places etc.

HTH

DominicB

 

[alecbowman]

Hi,

Have tried the following

If Format(filtertimedialog.Time_Min_Field, "dd mmm yyyy hh:mm:ss.000") >= Format(filtertimedialog.Time_Max_Field, "dd mmm yyyy hh:mm:ss.000") Then
ianswer = MsgBox("Min Value for Time/Serial Number Filter data greater than Max Value, Filter Cancelled", vbExclamation, "Filters Inconsistent")
Unload Me: End
End If

Where filtertimedialog.Time_Max_Field contained 27 Dec 1990 00:00:01.000 and filtertimedialog.Time_Max_Field contained 01 Jan 2000 00:00:130.

The expressions still seems to return True.

As for the strange format, the date/time is the timestamp applied by a data processing system driving amongst other things, a servo control. The interesting bits are usually where the time rolls over from on year to another or on startup where the default data/time is updated to current time. Actually Excel isn't really precise enough as the time is recorded in Posix format i.e. down to nanoseconds.