PING ... Bob Philips

[Jimbo]

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""),--(D2100>0),D2100)")


Thanks again

 

[Roger Whitehead]

=Date - Day(Date) + 1

 

[Jimbo]

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

 

[Bernie Deitrick]

Jimbo,

The correct way to do that is:

MsgBox Evaluate("SUMPRODUCT(--(A2:A100>=--" & startdate &
"),--(A2:A100<=--""2005-06-30""),
--(F2:F100=""Cleared""),--(D2100>0),D2100)")

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Jimbo,

I think he was suggesting a way to calculate the first day of the month. Not
very useful, since you are already doing that.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Jimbo,

Of course, you don't need the "Msgbox" part, and the line wrapping occurred
at a bad place.

Basically, replace

--(A2:A100>=--""2005-06-01"")

with

--(A2:A100>=--" & startdate & ")

HTH,
Bernie
MS Excel MVP

 

[Jimbo]

Thanks for your input Bernie ... I had tried that approach prior to starting
this thread but it doesn't appear to work ...
... hence my fiddling about with Format, Cdate, Cstr etc since the original
code (which works) contains a string representing the first of June 2005
albeit in yyyy-mm-dd format.
Where am I goijng wrong ?

Regards
Jimbo

I always seem to have trouble with dates ...

 

[Roger Whitehead]

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

 

[Roger Whitehead]

Sorry, that's:

Sorry, that's:

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
EndDate = Format(EndDate, "yyyy-mm-dd")
End Function

Watch out for the wrap

Roger

 

[Bernie Deitrick]

Jimbo,

The code worked for me in both its forms. I will send you a working version
privately.

HTH,
Bernie
MS Excel MVP