CODEWITHSUNDEEP
excelvbadate
Lelio Faieta
Lelio Faieta

Reputation: 6663

compare two dates in dd/mm/yyyy format

I have a column where I store dates as dd/mm/yyyy. Then I ask user to input in a form a new date (always as dd/mm/yyyy). I want to compare the input date to the last date that is in the excel already.

If Format(data_mov, "Short Date") < Format(data_last, "Short Date") Then

is not the way to go since it will compare two strings and the test will fail since 03/10/2018 looks smaller than 23/09/2018.

What is the correct way to test them? Something like converting them into timestamps and then compare (but is there something like the unixtimestamp in excel?)

Upvotes: 0

Views: 806

Answers (2)

p._phidot_
p._phidot_

Reputation: 1925

"What is the correct way to test them?"

IMHO, there is more than one way.

if() with value() will be straightforward.

if() with year(),month(),day(), hour().. is another.

"Something like converting them into timestamps and then compare (but is there something like the unixtimestamp in excel?)"

The easiest (for me) is using value() function . Then build the desired if() statement on it.

Hope it helps. (:

Microsoft Excel for Windows uses the 1900 date system, by default. Which means the first date is January 1, 1900. a Date and Time function can be used to manipulate the year/date and time/hour/minutes values. The date/time in Excel is stored as a number. Where the decimal part range from 0.0 to 0.99988426 represent 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.), and the integer part range from 0 to 9999 represent year 1900 to year 9999.

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272056

You convert the strings to date and compare them:

' dd/mm/yyyy
' 1234567890

Dim Date1: Date1 = DateSerial(Mid(data_mov, 7, 4), Mid(data_mov, 4, 2), Mid(data_mov, 1, 2))
Dim Date2: Date2 = DateSerial(Mid(data_last, 7, 4), Mid(data_last, 4, 2), Mid(data_last, 1, 2))

If Date1 < Date2 Then
    ' ...
End If

Upvotes: 2

Related Questions