=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