Comparing Date/Time

alecbowman

New Member
Joined
Sep 15, 2006
Messages
7
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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
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 :

Code:
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

New Member
Joined
Sep 15, 2006
Messages
7
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

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
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

New Member
Joined
Sep 15, 2006
Messages
7
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,811
Messages
5,544,448
Members
410,611
Latest member
JB_101920
Top