Comparing Dates In Excel VBA

[Mav3000]

Hi,

I have the following code which compares a date in a session of Excel to see if it falls into a specific date-range.

The date I am testing it on is "17/01/06", which is in a cell formatted to be a date in the format DD/MM/YYYY.

The code:

Do Until Excel.ActiveCell.Offset(0, -30).Value = ""
If FormatDateTime(Excel.ActiveCell.Value, vbShortDate) <> "" Then
If Excel.ActiveCell.Value > 31 / 12 / 2006 Then
If Excel.ActiveCell.Value < "01/02/2006" Then
'Add To ReportFormatDateTime6
ActiveCell.Value = ActiveCell.Value + 1
Excel.ActiveCell.Offset(1, 0).Select
Else
Excel.ActiveCell.Offset(1, 0).Select
End If
End If
End If
Loop

I have broken the if statement into three parts to see where it is going wrong. The first two parts work fine - going down to the next parts ok, however the final part doesn't work properly:

If Excel.ActiveCell.Value < "01/02/2006" Then...

Excel doesn't realise that the date (17/01/2006) is less than "01/02/2006".

What am I doing wrong??

 

[xlbo]

you are comparing a date to a text string. Try this:

If Excel.ActiveCell.Value > DATEVALUE("31/12/2006") Then
If Excel.ActiveCell.Value < DATEVALUE("01/02/2006") Then


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Mav3000]

Hi Geoff,

I have tried using the DataValue function as above, but it still doesn't work - now, the second line on the IF no longer works. It's not picking up that the cell value (whcih is reported as 17/01/2006) is not a date between the two parameters.

Have you any other ideas? My code is now:

Do Until Excel.ActiveCell.Offset(0, -30).Value = ""
If Excel.ActiveCell.Value <> "" Then
If DateValue(Excel.ActiveCell.Value) > DateValue("31/12/2006") Then
If DateValue(Excel.ActiveCell.Value) < DateValue("01/02/2006") Then
ActiveCell.Value = ActiveCell.Value + 1
Excel.ActiveCell.Offset(1, 0).Select
Else
Excel.ActiveCell.Offset(1, 0).Select
End If
Else
Excel.ActiveCell.Offset(1, 0).Select
End If
Else
Excel.ActiveCell.Offset(1, 0).Select
End If
Loop

 

[Mav3000]

This also doesn't work:

If Excel.ActiveCell.Value <> "" Then
If DateValue("17/01/2006") > DateValue("31/12/2006") Then
If DateValue("17/01/2006") < DateValue("01/02/2006") Then

 

[Mav3000]

If Excel.ActiveCell.Value <> "" Then
If DateValue(Excel.ActiveCell.Value) > DateValue("31/12/2005") Then
If DateValue(Excel.ActiveCell.Value) < DateValue("01/02/2006") Then

The above code now works! Geoff - your suggestion was correct, but I had put 31/01/2006 and not 31/01/2005!

Thanks for your help!

 

[xlbo]

no probs - glad you got a resolution

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question