Jimbo, my mistake (and an under-estimate by a few billion brain cells - as
Bernie has kindly confirmed.....!)

If I understand correctly, replace your ""2005-06-30"" with

" & StartDate & "

Then copy the following into a standard module.....

Function StartDate() As String
StartDate = Format(Date - Day(Date) + 1, "yyyy-mm-dd")
End Function

Then, for EndDate:

Function EndDate() As String
If Month(Date) = 12 Then
EndDate = Year(Date) + 1 & "-01-" & Format(Day(Date - Day(Date)
+ 1), "00")
Else
EndDate = Year(Date) & "-" & Format(Month(Date) + 1, "00") & "-"
& Format(Day(Date - Day(Date) + 1), "00")
End If
EndDate = CDate(EndDate) - 1
MsgBox EndDate
End Function


HTH
Roger




"Jimbo" wrote in message
...
Roger,
I'm not sure what you are suggesting ... Did you intend that your code was
to be used to establish the value of the variable StartDate or as a direct
substitute in the line of vbaCode.

In either case I would be grateful if you would reproduce the code and
also the equivalent for a variable EndDate (the end of current month)

My apologies if I am appearring negative or 'thick' its because I'm
becoming more confused by the hour

Regards



"Roger Whitehead" wrote in message
...
=Date - Day(Date) + 1


--
HTH
Roger
Shaftesbury (UK)
(A few miles further north than Bob P, and a few billion less brain
cells)



"Jimbo" wrote in message
...
Many thanks for your help in previous thread,

Now I would like to replace some of the criteria with variables but I am
stumped with the dates ... e.g. in the code below I would like to
replace "2005-06-01" with StartDate a variable which holds the value of
the first day of the current month

StartDate = Format(DateAdd("d", -Day(Now() - 1), Now()), "yyyy-mm-dd")

However many permutations of Cdate, Cstr, Format etc I cannot get it to
work correctly would you mind showing me how it can be done

........... original code ............

evaluate("SUMPRODUCT(--(A2:A100=--""2005-06-01""),--(A2:A100<=--""2005-06-30""),
--(F2:F100=""Cleared""),--(D2:D1000),D2:D100)")


Thanks again