Comparing dates with different date format in system

Question

I need to compare two Dates. They are in text format and they look like 30.05.2016, because they are extracted from other program. The problem is that on one system I got different date formatting (5/30/2016), than on another (30/5/2016).

I would like to know whether my thinking is in right direction, if not what should I do.

Firstly I will check which formatting do I have. If (5/30/2016) then I will do

1. Replace "." to "/" 
2. CDate(value)
3. NumberFormat = "General"
4. Comparing date1 < date2

If (30/5/2016) then I will do

1. DateValue(Replace "." to "/")

2. NumberFormat = "General"

3. Comparing date1 < date2

I am still thinking how to write this code, and your help on this stage would be nice.

Please try this select the column having date, Go to Data tab in Excel Select Text To Columns. There in the Text to Column Wizard 1st step select Delimited, 2nd step select none, 3rd step in Column data format options choose Date and if your date is like 5.27.2016 then choose accordingly MDY from drop down list.and then click finish button. It will correct the date to your system settings. For other date 27.5.2016 choose DMY and then finish button. If this effort does not succeed tell SO experts what results you are getting so that they may guide you for next steps whether VBA or a formula. — skkakkar
– skkakkar, Commented May 30, 2016 at 13:50
You don't have to modify the Dates, just create a Date variable for each of them and assign the strings with ´Format(datestring, "mm/dd/yyyy"´ — gizlmo
– gizlmo, Commented May 30, 2016 at 13:52

Gary's Student · Accepted Answer · 2016-05-30 14:28:01Z

1

This assumes that the date are actually in Text format. The first UDF() handles US-style dates:

Public Function IsD1LessThanD2(d1 As String, d2 As String) As Boolean
    ' US Date format
    IsD1LessThanD2 = CDate(Replace(d1, ".", "/")) < CDate(Replace(d2, ".", "/"))
End Function

The second UDF() handles European format:

Public Function IsD1LessThanD2_E(d1 As String, d2 As String) As Boolean
    ' European Date format
    ary1 = Split(d1, ".")
    ary2 = Split(d2, ".")
    d1 = DateValue(ary1(1) & "/" & ary1(0) & "/" & ary1(2))
    d2 = DateValue(ary2(1) & "/" & ary2(0) & "/" & ary2(2))
    IsD1LessThanD2_E = d1 < d2
End Function

answered May 30, 2016 at 14:28

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Damian Over a year ago

Well, It is very interesting solution, but very similar to mine. I will give it a try :)

raemaerne · Accepted Answer · 2016-05-30 14:26:59Z

0

You can format both strings to the Date format

Dim date1, date2 As Date
' string1 in the format 5/30/2016
date1 = Format(string1, "mm/dd/yyyy")
' string2 in the format 30/5/2016
date2 = Format(string2, "dd/mm/yyyy")

And then you can simply compare the dates.

answered May 30, 2016 at 14:26

raemaerne

1128 bronze badges

Collectives™ on Stack Overflow

Comparing dates with different date format in system

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related