Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Mav3000 (TechnicalUser)
15 May 06 7:12
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 (MIS)
15 May 06 7:15
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 (TechnicalUser)
15 May 06 7:23
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 (TechnicalUser)
15 May 06 7:26
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 (TechnicalUser)
15 May 06 7:34
        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 (MIS)
15 May 06 9:26
no probs - glad you got a resolution thumbsup

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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close