Check date 1 is before date 2 ...

adambc

Board Regular
Joined
Jan 13, 2020
Messages
155
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I have a UserForm that creates a new record - which calls another UserFDorm (CalendarForm - Developed by Trevor Eyre) as follows ...

VBA Code:
Function GetFormattedDate() As String
Dim dateVariable As Date
dateVariable = CalendarForm.GetDate

If dateVariable <> 0 Then
GetFormattedDate = Format$(dateVariable, "dd/mm/yyyy")
End If
End Function

Note: CalendarForm returns the date in US format - hence the Format command to turn it into UK format ...

Which is followed by ...

VBA Code:
Private Sub cmdReportedDate_Click()

Me.txtReportedDate.Text = GetFormattedDate()

End Sub
Private Sub cmdIncidentDate_Click()

Me.txtIncidentDate.Text = GetFormattedDate()

End Sub

Note: both work as intended/expected ...

And then when the Save button is clicked ...

VBA Code:
If txtReportedDate < txtIncidentDate Then
    MsgBox "The Reported Date must be on or after the Incident Date, please correct and Save again"
    Exit Sub
End If

But unless both dates are in the same month (at least I think that's the trigger), the validation fails ...

I can't see why - any ideas???

Thanks ...
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

adambc

Board Regular
Joined
Jan 13, 2020
Messages
155
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
ERRATUM ...

And then when the Save button is clicked ...

VBA Code:
Today = Format$(Date, "dd/mm/yyyy")

If txtIncidentDate > Today Then
    MsgBox "The Incident Date must be today or earlier, please correct and Save again"
    Exit Sub
End If

If txtReportedDate < txtIncidentDate Then
    MsgBox "The Reported Date must be on or after the Incident Date, please correct and Save again"
    Exit Sub
End If
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
Note: CalendarForm returns the date in US format - hence the Format command to turn it into UK format ...
The issue is that the FORMAT function returns a text value, not a date one.
So you are comparing two text strings, not two dates (text strings will follow alphabetic sorting rules).

You need to either change those values back to dates for purposes of doing the comparison, or choose a format in which the alphabetic sorting rules would also match date sorting rules, i.e.
choosing a date format like "yyyy/mm/dd".
 

adambc

Board Regular
Joined
Jan 13, 2020
Messages
155
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
The issue is that the FORMAT function returns a text value, not a date one.
So you are comparing two text strings, not two dates (text strings will follow alphabetic sorting rules).

You need to either change those values back to dates for purposes of doing the comparison, or choose a format in which the alphabetic sorting rules would also match date sorting rules, i.e.
choosing a date format like "yyyy/mm/dd".
@Joe4

Thanks for your speedy reply ...

So could I rewrite the validation (I must have the dd/mm/yyyy format on the UserForm and in the underlying table)? ...

VBA Code:
Today = Format$(Date, "dd/mm/yyyy")

If Format$(txtIncidentDate, "dd/mm/yyyy") > Today Then
    MsgBox "The Incident Date must be today or earlier, please correct and Save again"
    Exit Sub
End If

If Format$(txtReportedDate, "dd/mm/yyyy") < Format$(txtIncidentDate, "dd/mm/yyyy") Then
    MsgBox "The Reported Date must be on or after the Incident Date, please correct and Save again"
    Exit Sub
End If
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

So could I rewrite the validation (I must have the dd/mm/yyyy format on the UserForm and in the underlying table)? ...
I don't think you undestand what I am telling you.
The FORMAT$ function ALWAYS returns text values, not date values!

So, if you use it in comparisons like you are trying to do, you are going to get unexpected results, because text comparisons follow alphabetic rules.
A value like "01/01/2020" will be less considered than "12/31/1999" because when you look at the first characters of each, "0" comes before "1".

If you wanted to keep using the FORMAT$ function in your code, then you could get it to work right ONLY if you use a format like "yyyy/mm/dd" (where year is always first, then month, and days last), as that will make the dates look like "2020/01/01" and "1999/12/31", and those will sort the way you want.

Otherwise, you will need to convert the values you want to compare in your expressions to dates, by using something like the CDATE function (MS Excel: How to use the CDATE Function (VBA)).
 

adambc

Board Regular
Joined
Jan 13, 2020
Messages
155
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
@Joe4

Thanks for your speedy reply ...

So could I rewrite the validation (I must have the dd/mm/yyyy format on the UserForm and in the underlying table)? ...

VBA Code:
Today = Format$(Date, "dd/mm/yyyy")

If Format$(txtIncidentDate, "dd/mm/yyyy") > Today Then
    MsgBox "The Incident Date must be today or earlier, please correct and Save again"
    Exit Sub
End If

If Format$(txtReportedDate, "dd/mm/yyyy") < Format$(txtIncidentDate, "dd/mm/yyyy") Then
    MsgBox "The Reported Date must be on or after the Incident Date, please correct and Save again"
    Exit Sub
End If
IGNORE MY PREVIOUS REPLY ...

This seems to work ie changing format to yyyy/mm/dd for the purpose of validation, but keeping dd/mm/yyyy for the UserForm/underlying table ...

Or is their a better way???

VBA Code:
Today = Format$(Date, "yyyy/mm/dd")

If Format$(txtIncidentDate, "yyyy/mm/dd") > Today Then
    MsgBox "The Incident Date must be today or earlier, please correct and Save again"
    Exit Sub
End If

If Format$(txtReportedDate, "yyyy/mm/dd") < Format$(txtIncidentDate, "yyyy/mm/dd") Then
    MsgBox "The Reported Date must be on or after the Incident Date, please correct and Save again"
    Exit Sub
End If
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

This seems to work ie changing format to yyyy/mm/dd for the purpose of validation, but keeping dd/mm/yyyy for the UserForm/underlying table ...

Or is their a better way???
As I mentioned in my previous post, you can do either (the way you did it or by converting the values back to dates for comparison purposes).
Because VBA uses American date formats, if you are in the UK, I think the way you did it may be a bit more reliable, as I am not sure if doing a date conversion will always work properly with the competing US/UK formats.

Just be sure if you use the FORMAT$ function with the a date format, always use "yyyy/mm/dd".
It is important to have the four digit year, two digit month, and 2 digit day to make sure sorting works properly.
For example, if you used 2 digit years, "20/01/01" will come before "99/01/01", which is not correct for your comparisions.
 
Solution

adambc

Board Regular
Joined
Jan 13, 2020
Messages
155
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
@Joe4

Many thanks again - yet again Mr Excel contributors come to my rescue!!!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,426
Messages
5,601,587
Members
414,460
Latest member
uctc

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