Date Comparison - An Easier Way?

[Gordon Bentley-Mix at news.microsoft.com]

Many times in a UserForm I find I have to validate a date value entered into
a TextBox control to make sure it is either before (or on) or after (or on) a
certain date - usually the current date. I usually do this by using CDate to
convert the value entered in the TextBox to a date and then comparing it to
the value of 'Now'. However, because 'Now' includes the time, I sometimes get
"false negatives" - especially if the value has to be _on or after_ the
current date - because CDate converts the TextBox value to '[date] 00:00:00'
while 'Now' converts to '[current date] [current time]'.

For example, as I write this, it is 12:47 pm on 9 March 2009. If I enter a
value of "9 March 2009" in the TextBox and I want to ensure that the value is
on or after the current date, the comparison returns false because CDate
converts the value to '9 March 2009 00:00:00', is _before_ 9 March 2009
12:47:ss.

To get around this, I've tried adding or subtracting half a day (or even a
full day) to 'Now', but I've found this to be a bit risky and subject to
failure under certain conditions. (Exactly what the conditions are that cause
the problem is immaterial, but it definitely doesn't always work reliably.)
Consequently, I've resorted to using a construction like:

If CDate(txtMyDate.Value) > CDate(Format(Now, "d mm yy")) Then
'Throw an error
End If

In this construction, if I enter "9 March 2009" in 'txtMyDate', CDate
returns "9 March 2009 00:00:00" for the value from the TextBox and "9 March
2009 00:00:00" for the formatted value of 'Now', and the validation passes.
However, this seems a bit clunky, and I'm wondering if there's an easier way
to do this comparison without resorting to the Format function to strip out
the time part of 'Now'. Suggestions?
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!

 

[macropod]

Hi Gordon,

You could use Int(Now) ...

 

[Greg Maxey]

Gordon,

Either of these work for me:

Dim dDate As Date
If IsDate(Me.TextBox1) Then
dDate = CDate(Me.TextBox1) + Time
Select Case True
Case dDate = Now
MsgBox "Dates are same"
Case dDate < Now
MsgBox "Before"
Case dDate > Now
MsgBox "After"
End Select
End If

or (an I think this is because Date on my system uses the short date
format):

Dim dDate As Date
If IsDate(Me.TextBox1) Then
dDate = CDate(Me.TextBox1)
Select Case True
Case dDate = Date
MsgBox "Dates are same"
Case dDate < Date
MsgBox "Before"
Case dDate > Date
MsgBox "After"
End Select
End If

Many times in a UserForm I find I have to validate a date value
entered into a TextBox control to make sure it is either before (or
on) or after (or on) a certain date - usually the current date. I
usually do this by using CDate to convert the value entered in the
TextBox to a date and then comparing it to the value of 'Now'.
However, because 'Now' includes the time, I sometimes get "false
negatives" - especially if the value has to be _on or after_ the
current date - because CDate converts the TextBox value to '[date]
00:00:00' while 'Now' converts to '[current date] [current time]'.

For example, as I write this, it is 12:47 pm on 9 March 2009. If I
enter a value of "9 March 2009" in the TextBox and I want to ensure
that the value is on or after the current date, the comparison
returns false because CDate converts the value to '9 March 2009
00:00:00', is _before_ 9 March 2009 12:47:ss.

To get around this, I've tried adding or subtracting half a day (or
even a full day) to 'Now', but I've found this to be a bit risky and
subject to failure under certain conditions. (Exactly what the
conditions are that cause the problem is immaterial, but it
definitely doesn't always work reliably.) Consequently, I've resorted
to using a construction like:

If CDate(txtMyDate.Value) > CDate(Format(Now, "d mm yy")) Then
'Throw an error
End If

In this construction, if I enter "9 March 2009" in 'txtMyDate', CDate
returns "9 March 2009 00:00:00" for the value from the TextBox and "9
March 2009 00:00:00" for the formatted value of 'Now', and the
validation passes. However, this seems a bit clunky, and I'm
wondering if there's an easier way to do this comparison without
resorting to the Format function to strip out the time part of 'Now'.
Suggestions?

 

[Gordon Bentley-Mix at news.microsoft.com]

Interesting Paul...

It does work - returns "9/03/2009" (formatted according to system settings,
of course) - but I'm not quite sure why. I assume it's because behind the
scenes 'Now' is actually some sort of decimal value based on the number of
days since the beginning of (MSFT) time. Am I close? ;-P

In the course of poking around in the help looking for info on the Int
function, I came across something else that might actually work even better:
the Date function. According to the help, it "[r]eturns a Variant (Date)
containing the current system date." Typing ?Date in the immediate window
also returns "9/03/2009", which is what I want and has the added advantage of
not requiring an argument like the Int function does.

Thanks for putting me on the right track. It looks like either of these will
be considerably more elegant than converting the formatted value of 'Now' to
date value.
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!

Hi Gordon,

You could use Int(Now) ...

--
Cheers
macropod
[MVP - Microsoft Word]

Many times in a UserForm I find I have to validate a date value entered into
a TextBox control to make sure it is either before (or on) or after (or on) a
certain date - usually the current date. I usually do this by using CDate to
convert the value entered in the TextBox to a date and then comparing it to
the value of 'Now'. However, because 'Now' includes the time, I sometimes get
"false negatives" - especially if the value has to be _on or after_ the
current date - because CDate converts the TextBox value to '[date] 00:00:00'
while 'Now' converts to '[current date] [current time]'.

For example, as I write this, it is 12:47 pm on 9 March 2009. If I enter a
value of "9 March 2009" in the TextBox and I want to ensure that the value is
on or after the current date, the comparison returns false because CDate
converts the value to '9 March 2009 00:00:00', is _before_ 9 March 2009
12:47:ss.

To get around this, I've tried adding or subtracting half a day (or even a
full day) to 'Now', but I've found this to be a bit risky and subject to
failure under certain conditions. (Exactly what the conditions are that cause
the problem is immaterial, but it definitely doesn't always work reliably.)
Consequently, I've resorted to using a construction like:

If CDate(txtMyDate.Value) > CDate(Format(Now, "d mm yy")) Then
'Throw an error
End If

In this construction, if I enter "9 March 2009" in 'txtMyDate', CDate
returns "9 March 2009 00:00:00" for the value from the TextBox and "9 March
2009 00:00:00" for the formatted value of 'Now', and the validation passes.
However, this seems a bit clunky, and I'm wondering if there's an easier way
to do this comparison without resorting to the Format function to strip out
the time part of 'Now'. Suggestions?
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!

 

[Gordon Bentley-Mix at news.microsoft.com]

SNAP!

As you'll see in my reply to Paul, I found the Date function on my own, and
it seems to work a treat.

Final construction looks something like this:

Private Sub txtMyDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.Visible = True Then
Cancel = Not fcnValidateMyDate
If Cancel = False Then
bSuppressError = False
FormatMyDate
End If
End If
End Sub

Private Function fcnValidateMyDate() As Boolean
fcnValidateMyDate = True
If Len(txtMyDate.Value) > 0 Then
If IsDate(txtMyDate.Value) = False Then
GoTo MyDateFail
ElseIf CDate(txtMyDate.Value) < Date Then
GoTo MyDateFail
End If
End If
Exit Function

MyDateFail:
MsgBox "The Date must be a valid date on or after the current date of "
& Date & ".", vbCritical, "Date Error"
fcnValidateMyDate = False
End Function

Private Sub FormatMyDate()
If Len(txtMyyDate.Value) > 0 Then txtMyDate.Value =
Format(txtMyDate.Value, "d/mm/yyyy")
End Sub

It's structured this way because it's a multipage UserForm and I call the
validation and formatting in a couple of different places - in the Exit event
of the TextBox, as well as in the Click event of the 'OK' button. (Just
because it's easier than trying to do anything around the Change event of the
MultiPage control...)

And "bSuppressError" is just a mechanism for making sure that the error
message isn't displayed a second time when you select the appropriate Page to
correct the problem - 'cause the only time you _should_ get this error in the
'OK' button Click is if you've selected another Page while focus was still
set to the TextBox that contained the invalid value, and then when you go
back to fix the error, focus gets set to the first control on the Page and
the Exit validation fires, which generates the error message a second time...

It looks a bit like this (cut down _considerably_ for demonstration purposes):

Private Sub btnOK_Click()
If fcnValidateInput = True Then Me.Hide
End Sub

Private Function fcnValidateInput() As Boolean
fcnValidateInput = True
If Len(txtMyDate.Value) = 0 Then
MsgBox "Please enter the Date.", vbCritical, "Date Error"
fcnValidateInput = False
Exit Function
ElseIf fcnValidateMyDate = False Then
bSuppressError = True
fcnValidateInput = False
Exit Function
Else: FormatMyDate
End If
End Function

Got all that? ;-D
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!

Gordon,

Either of these work for me:

Dim dDate As Date
If IsDate(Me.TextBox1) Then
dDate = CDate(Me.TextBox1) + Time
Select Case True
Case dDate = Now
MsgBox "Dates are same"
Case dDate < Now
MsgBox "Before"
Case dDate > Now
MsgBox "After"
End Select
End If

or (an I think this is because Date on my system uses the short date
format):

Dim dDate As Date
If IsDate(Me.TextBox1) Then
dDate = CDate(Me.TextBox1)
Select Case True
Case dDate = Date
MsgBox "Dates are same"
Case dDate < Date
MsgBox "Before"
Case dDate > Date
MsgBox "After"
End Select
End If

Many times in a UserForm I find I have to validate a date value
entered into a TextBox control to make sure it is either before (or
on) or after (or on) a certain date - usually the current date. I
usually do this by using CDate to convert the value entered in the
TextBox to a date and then comparing it to the value of 'Now'.
However, because 'Now' includes the time, I sometimes get "false
negatives" - especially if the value has to be _on or after_ the
current date - because CDate converts the TextBox value to '[date]
00:00:00' while 'Now' converts to '[current date] [current time]'.

For example, as I write this, it is 12:47 pm on 9 March 2009. If I
enter a value of "9 March 2009" in the TextBox and I want to ensure
that the value is on or after the current date, the comparison
returns false because CDate converts the value to '9 March 2009
00:00:00', is _before_ 9 March 2009 12:47:ss.

To get around this, I've tried adding or subtracting half a day (or
even a full day) to 'Now', but I've found this to be a bit risky and
subject to failure under certain conditions. (Exactly what the
conditions are that cause the problem is immaterial, but it
definitely doesn't always work reliably.) Consequently, I've resorted
to using a construction like:

If CDate(txtMyDate.Value) > CDate(Format(Now, "d mm yy")) Then
'Throw an error
End If

In this construction, if I enter "9 March 2009" in 'txtMyDate', CDate
returns "9 March 2009 00:00:00" for the value from the TextBox and "9
March 2009 00:00:00" for the formatted value of 'Now', and the
validation passes. However, this seems a bit clunky, and I'm
wondering if there's an easier way to do this comparison without
resorting to the Format function to strip out the time part of 'Now'.
Suggestions?

 

[Greg Maxey]

Of course!

SNAP!

As you'll see in my reply to Paul, I found the Date function on my
own, and it seems to work a treat.

Final construction looks something like this:

Private Sub txtMyDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.Visible = True Then
Cancel = Not fcnValidateMyDate
If Cancel = False Then
bSuppressError = False
FormatMyDate
End If
End If
End Sub

Private Function fcnValidateMyDate() As Boolean
fcnValidateMyDate = True
If Len(txtMyDate.Value) > 0 Then
If IsDate(txtMyDate.Value) = False Then
GoTo MyDateFail
ElseIf CDate(txtMyDate.Value) < Date Then
GoTo MyDateFail
End If
End If
Exit Function

MyDateFail:
MsgBox "The Date must be a valid date on or after the current date
of " & Date & ".", vbCritical, "Date Error"
fcnValidateMyDate = False
End Function

Private Sub FormatMyDate()
If Len(txtMyyDate.Value) > 0 Then txtMyDate.Value =
Format(txtMyDate.Value, "d/mm/yyyy")
End Sub

It's structured this way because it's a multipage UserForm and I call
the validation and formatting in a couple of different places - in
the Exit event of the TextBox, as well as in the Click event of the
'OK' button. (Just because it's easier than trying to do anything
around the Change event of the MultiPage control...)

And "bSuppressError" is just a mechanism for making sure that the
error message isn't displayed a second time when you select the
appropriate Page to correct the problem - 'cause the only time you
_should_ get this error in the 'OK' button Click is if you've
selected another Page while focus was still set to the TextBox that
contained the invalid value, and then when you go back to fix the
error, focus gets set to the first control on the Page and the Exit
validation fires, which generates the error message a second time...

It looks a bit like this (cut down _considerably_ for demonstration
purposes):

Private Sub btnOK_Click()
If fcnValidateInput = True Then Me.Hide
End Sub

Private Function fcnValidateInput() As Boolean
fcnValidateInput = True
If Len(txtMyDate.Value) = 0 Then
MsgBox "Please enter the Date.", vbCritical, "Date Error"
fcnValidateInput = False
Exit Function
ElseIf fcnValidateMyDate = False Then
bSuppressError = True
fcnValidateInput = False
Exit Function
Else: FormatMyDate
End If
End Function

Got all that? ;-D

Gordon,

Either of these work for me:

Dim dDate As Date
If IsDate(Me.TextBox1) Then
dDate = CDate(Me.TextBox1) + Time
Select Case True
Case dDate = Now
MsgBox "Dates are same"
Case dDate < Now
MsgBox "Before"
Case dDate > Now
MsgBox "After"
End Select
End If

or (an I think this is because Date on my system uses the short date
format):

Dim dDate As Date
If IsDate(Me.TextBox1) Then
dDate = CDate(Me.TextBox1)
Select Case True
Case dDate = Date
MsgBox "Dates are same"
Case dDate < Date
MsgBox "Before"
Case dDate > Date
MsgBox "After"
End Select
End If

Many times in a UserForm I find I have to validate a date value
entered into a TextBox control to make sure it is either before (or
on) or after (or on) a certain date - usually the current date. I
usually do this by using CDate to convert the value entered in the
TextBox to a date and then comparing it to the value of 'Now'.
However, because 'Now' includes the time, I sometimes get "false
negatives" - especially if the value has to be _on or after_ the
current date - because CDate converts the TextBox value to '[date]
00:00:00' while 'Now' converts to '[current date] [current time]'.

For example, as I write this, it is 12:47 pm on 9 March 2009. If I
enter a value of "9 March 2009" in the TextBox and I want to ensure
that the value is on or after the current date, the comparison
returns false because CDate converts the value to '9 March 2009
00:00:00', is _before_ 9 March 2009 12:47:ss.

To get around this, I've tried adding or subtracting half a day (or
even a full day) to 'Now', but I've found this to be a bit risky and
subject to failure under certain conditions. (Exactly what the
conditions are that cause the problem is immaterial, but it
definitely doesn't always work reliably.) Consequently, I've
resorted to using a construction like:

If CDate(txtMyDate.Value) > CDate(Format(Now, "d mm yy")) Then
'Throw an error
End If

In this construction, if I enter "9 March 2009" in 'txtMyDate',
CDate returns "9 March 2009 00:00:00" for the value from the
TextBox and "9 March 2009 00:00:00" for the formatted value of
'Now', and the validation passes. However, this seems a bit clunky,
and I'm wondering if there's an easier way to do this comparison
without resorting to the Format function to strip out the time part
of 'Now'. Suggestions?

 

[macropod]

Hi Gordon,

Dates & times returned by Now consist of an integer part for the date value and a decimal part representing the percentage elapsed
of 24 hours.

--
Cheers
macropod
[MVP - Microsoft Word]

Interesting Paul...

It does work - returns "9/03/2009" (formatted according to system settings,
of course) - but I'm not quite sure why. I assume it's because behind the
scenes 'Now' is actually some sort of decimal value based on the number of
days since the beginning of (MSFT) time. Am I close? ;-P

In the course of poking around in the help looking for info on the Int
function, I came across something else that might actually work even better:
the Date function. According to the help, it "[r]eturns a Variant (Date)
containing the current system date." Typing ?Date in the immediate window
also returns "9/03/2009", which is what I want and has the added advantage of
not requiring an argument like the Int function does.

Thanks for putting me on the right track. It looks like either of these will
be considerably more elegant than converting the formatted value of 'Now' to
date value.
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!

Hi Gordon,

You could use Int(Now) ...

--
Cheers
macropod
[MVP - Microsoft Word]

Many times in a UserForm I find I have to validate a date value entered into
a TextBox control to make sure it is either before (or on) or after (or on) a
certain date - usually the current date. I usually do this by using CDate to
convert the value entered in the TextBox to a date and then comparing it to
the value of 'Now'. However, because 'Now' includes the time, I sometimes get
"false negatives" - especially if the value has to be _on or after_ the
current date - because CDate converts the TextBox value to '[date] 00:00:00'
while 'Now' converts to '[current date] [current time]'.

For example, as I write this, it is 12:47 pm on 9 March 2009. If I enter a
value of "9 March 2009" in the TextBox and I want to ensure that the value is
on or after the current date, the comparison returns false because CDate
converts the value to '9 March 2009 00:00:00', is _before_ 9 March 2009
12:47:ss.

To get around this, I've tried adding or subtracting half a day (or even a
full day) to 'Now', but I've found this to be a bit risky and subject to
failure under certain conditions. (Exactly what the conditions are that cause
the problem is immaterial, but it definitely doesn't always work reliably.)
Consequently, I've resorted to using a construction like:

If CDate(txtMyDate.Value) > CDate(Format(Now, "d mm yy")) Then
'Throw an error
End If

In this construction, if I enter "9 March 2009" in 'txtMyDate', CDate
returns "9 March 2009 00:00:00" for the value from the TextBox and "9 March
2009 00:00:00" for the formatted value of 'Now', and the validation passes.
However, this seems a bit clunky, and I'm wondering if there's an easier way
to do this comparison without resorting to the Format function to strip out
the time part of 'Now'. Suggestions?
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!