Getting Unexpected Results using AND function

[JAlbers]

Greetings all.

I have a column of dates in a table that I am wanting to make selections from. My selection I think is relatively straight forward and none to complex (or I wouldn't have come up with it) =AND($A4>= BegDate,$A4<=EndDate) This is returning FALSE even when A4 is clearing between the beginning and ending dates.

I have tried writing the formula manually using the actual dates and get the same response "FALSE". I must be missing something but I do not see what it is.

Any help would be greatly appreciated!

Thanks

Jake

 

[shg]

It wouldn't matter if there were.

 

[Norie]

What exactly are BegDate and EndDate?

If this is a formula on a worksheet are they named ranges?

 

[Gary's Student]

Make sure the variables are Dim'ed as Date. MsgBox them to make sure the values are proper.

 

[JAlbers]

What exactly are BegDate and EndDate?

If this is a formula on a worksheet are they named ranges?

BegDate & EndDate are variables within a macro. Both are declared as Date. BegDate is user input and (EndDate = BegDate + 6). This marks a week's worth of production records for selection. This was to have been an improvement over the previous selection criteria that based the selection off of the Today() value. This would let me choose a beginning date for the report and it would give me a week of data.

When I use the formula (written with actual dates rather than my variable names) I get the same problem, however, if I base the AND function on the TODAY() value, it seems to work perfectly. After running my macro, I have gone in and looked at the formula it wrote to the worksheet and it has the correct values for the variables.

I want to thank everyone for their help. I am sure this is going to end up being one of those problems where I just wasn't holding my tongue correctly when I wrote the macro. Only through struggle does growth come.

Jake

 

[Norie]

Jake

How and where are you using the formula =AND($A4>= BegDate,$A4<=EndDate) ?

It can't be in VBA, among other things the syntax for AND is wrong, and it can't be on a worksheet either because BegDate and EndDate are variables.

One possibility is that you are using code to write this formula to a worksheet, and you are replacing EndData and BegDate.

If you are doing that post the code.

 

[JAlbers]

Here is the code:

Sub dateselect()
Dim bdate As Date
Dim edate As Date
Dim hdate As Date
Dim Msg As String
Dim Title As String
Dim config As Integer
Dim ans As Integer
    On Error Resume Next
    Application.DisplayAlerts = False
    bdate = Application.InputBox _
        (prompt:="Enter beginning date for report here.", _
            Title:="Beginning Date Input", Type:=1)
    Msg = "Do you wish to run the Production Summary Report?"
    Title = "Report Date Confirmation"
    config = vbYesNo + vbQuestion
    ans = MsgBox(Msg, config, Title)
    If ans = vbYes Then GoTo report
    MsgBox "The Procedure has been TERMINATED by USER"
Exit Sub
report:
    Range("D10").Formula = _
        "=AND((A10>=" & bdate & "),(A10<=" & edate & "))"
    Range("D10").Copy
    Range("D11:D60").PasteSpecial Paste:=(xlFormulas)
' D60 would be replaced by D & last row
End Sub

 

[Norie]

You can't write dates like that in an Excel formula, they are actually treated as an expression - dividing the day by month (or vice versa) then dividing by year.

For example I put 13/4/2012 in the formula and it was evaluated as 0.00161530815109344.

There are various ways you could deal with this.

You could put the dates in a cell and reference the cells in the formula, or you could wrap the date(s) in the DATEVALUE function.

I've done the latter in this code.

Sub dateselect()
Dim bdate As Date
Dim edate As Date
Dim hdate As Date
Dim Msg As String
Dim Title As String
Dim config As Integer
Dim ans As Integer


    bdate = Application.InputBox _
            (prompt:="Enter beginning date for report here.", _
             Title:="Beginning Date Input", Type:=1)
             
    edate = Application.InputBox _
            (prompt:="Enter end date for report here.", _
             Title:="Beginning Date Input", Type:=1)
             
    Msg = "Do you wish to run the Production Summary Report?"
    Title = "Report Date Confirmation"
    config = vbYesNo + vbQuestion
    ans = MsgBox(Msg, config, Title)
    
    If ans = vbNo Then
        MsgBox "The Procedure has been TERMINATED by USER"
        Exit Sub
    End If

    Range("D10~:D60").Formula = _
    "=AND((A10>=DATEVALUE(""" & bdate & """)),(A10<=DATEVALUE(""" & edate & """)))"
    
End Sub

PS I also added an inputbox to get edate.

 

[Marcelo Branco]

Maybe

report:
    edate = bdate + 6
    Range("D10:D60").Formula = _
        "=AND((A10>=" & [B]Clng[/B](bdate) & "),(A10<=" & [B]Clng[/B](edate) & "))"

M.

 

[JAlbers]

Thanks Norie! That's great. That must be why it worked when I was calculating off the TODAY() function. Each day we add a little bit to our box o' knowledge. Thanks again!