IntraCell Date Comparison

poorwallace

Active Member
Joined
Mar 8, 2005
Messages
365
Hello all....I've got a question about comparing 2 dates. The problem is that the date is contained within a cell in this format:

NNGGQ 12/21/2005

I'm trying to compare the date in the right of the cell to the date in another cell using this:

Code:
If Right(Cells(4, 4), 8) = Range("G1") Then
    MsgBox "It's today!"
ElseIf Right(Cells(4, 4), 8) <> Range("G1") Then
    MsgBox "It's not today!"
End If


From the immediate window, I'm getting this:

Code:
?Right(Cells(4, 4), 8)
12/21/05
?Range("G1")
12/21/2005

I suspect the problem is that Range G1 is formatted as a date, while the Right(Cells...) bit is returning text. I could create another cell to output the results of the Right(Cells), but I'd like to see if anyone has an easier solution.

Thanks
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
Code:
If CDate(Right(Cells(4, 4), 8)) = Range("G1") Then
    MsgBox "It's today!"
Else
    MsgBox "It's not today!"
End If

Regards,
 

poorwallace

Active Member
Joined
Mar 8, 2005
Messages
365
Barrie,

You've helped me again! What just happened? I'm not familiar with CDate...

Thanks!
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Why are you pulling 8 characters when your date is in a 10 character format?

Code:
Sub test()
If DateValue(Right(Range("D4"), 10)) = Range("G1") Then
    MsgBox "It's today!"
Else
    MsgBox "It's not today!"
End If
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,088
Office Version
  1. 365
Platform
  1. Windows
Try using DateValue to convert to a 'real' date.
 

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
poorwallace said:
Barrie,

You've helped me again! What just happened? I'm not familiar with CDate...

Thanks!

Cdate will attempt to convert a string variable to a date variable.

Regards,
 

Watch MrExcel Video

Forum statistics

Threads
1,119,144
Messages
5,576,322
Members
412,716
Latest member
thviid
Top