CODEWITHSUNDEEP
vbaexcel
bd528
bd528

Reputation: 886

Comparing string date to actual date in Excel 2016

I have a date in cell A1 in text format - May 18

Using VBA, is it possible, to positively compare this value to the value in cell A2 - 01/05/2018 (UK date format)?

I've tried using the CDate function, but it does not return the correct value. For example, If the value in A1 was Nov 24, CDate reurns 24/11/2018.

So, what I want to do is :- If A1 = May 18 AND A2 = 01/05/2018 THEN TRUE

Upvotes: 0

Views: 734

Answers (3)

Gary's Student
Gary's Student

Reputation: 96753

With VBA, consider:

Sub DateCompaison()
    Dim s1 As String, s2 As String, d2 As Date
    s1 = [A1].Text
    s2 = [A2].Text

    arry1 = Split(s1, " ")
    arry2 = Split(s2, "/")

    month1 = arry1(0)
    year1 = "20" & arry1(1)

    d2 = DateSerial(CInt(arry2(2)), CInt(arry2(1)), CInt(arry2(0)))
    month2 = Format(d2, "mmmm")
    year2 = CStr(Year(d2))

    If month1 = month2 And year1 = year2 Then
        MsgBox "the same"
    Else
        MsgBox "not the same"
    End If
End Sub

enter image description here

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

You could use DATEVALUE: 

=DATEVALUE("1 " & $A$1)=$A$2

Will return TRUE if 1st of the month in cell A1 is the same as the date in cell A2.

Upvotes: 3

jmdon
jmdon

Reputation: 1017

You can use an Excel formula: 

=TEXT("18 May","dd-mm-yyyy")

Or in VBA:

Dim Dt As Date
Dt = CDate(Range("A1"))
Dim Compare As Boolean
Compare = Dt = Range("A2")

Upvotes: 1

Related Questions