Date & Time Calculation

[Vikram Dhemare]

I am trying to evaluate sumproduct function with multiple conditions wherein
the system not satisfying the date condition. Can anybody help.
The columns I have is
A
Date & Time
27/09/08 18:52
27/09/08 18:52
30/09/08 18:52
I am trying the follwign Code
s = "Sumproduct((" & r & rngE.Address & "=""" & partno & """)*" & _
"(" & r & rngB.Address & ">=" & CDbl(opstk) & ")," & _
"(" & r & rngJ.Address & "))"
MsgBox s
m = Evaluate(s)
But the date condition is not satisfying in this case.
The variable is defined as date. Is I am going wrong. Help needed.

 

[Bernie Deitrick]

Vikram,

I am trying the follwign Code

Your code example is incomplete? Where do you define the ranges? What is r? What is the type and
value of opstk? What is the value of s after the code runs your first line?

Perhaps, you could change

">=" & CDbl(opstk) & "),"

to this , if opstk is a number

">=DATEVALUE(" & Format(opstk,"mmm dd, yyyy") & "),"

or to this, if opstk is a date string

">=DATEVALUE(" & opstk & "),"

Answer my other questions and we'll go from there....


HTH,
Bernie
MS Excel MVP