CODEWITHSUNDEEP
vbaexcel
Damian
Damian

Reputation: 5

Comparing dates with different date format in system

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.

Upvotes: 0

Views: 5942

Answers (2)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

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

enter image description here

Upvotes: 1

raemaerne
raemaerne

Reputation: 112

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.

Upvotes: 0

Related Questions