View Full Version : Solved: Date calculation in VBA

12-31-2012, 02:44 AM
Dear all,

I am facing a strange problem with the Date calculation in VBA. To specify my problem, please open my attachment and run the Calc() macro.

Now if I point to the code in VBA 'Format(Worksheets("Sheet1").Cells(23, 1), "mm/dd/yyyy") > Today_Date', I was expecting to get 'TRUE' instead I got 'FALSE'.

Can somebody point me where I am going wrong?

Thanks and regards,

12-31-2012, 03:44 AM
You're comparing formatted strings and since 12 > 01, you get false. Use
Sub Calc()
Today_Date = Date
MsgBox Today_Date
MsgBox Worksheets("Sheet1").Cells(23, 1)
MsgBox Worksheets("Sheet1").Cells(23, 1) > Today_Date
End Sub

12-31-2012, 06:53 AM
I wouldn't mess around with testing based on formatting. Could be unreliable depending on settings, etc.

Option Explicit
Sub Calc()

' MsgBox Worksheets("Sheet1").Cells(23, 1).Value
' MsgBox Worksheets("Sheet1").Cells(23, 1).Value2
MsgBox CLng(Worksheets("Sheet1").Cells(23, 1).Value2) > CLng(Now)
End Sub