date > date function

  • Thread starter Thread starter Ann
  • Start date Start date
A

Ann

hi, i'm trying to look in column A for a date that falls within a specific
time frame, then look in column B to see if that date is > than the date in
column A, if it is, then add the qty in column C.
ex:
A1: 5/22/08 B1: 5/23/08 C1: 4
A2: 5/23/08 B2: 5/23/08 C2: 2

so if formula is look in col A for all dates within 5/19 and 5/22, then i
want the result to show 4.
tia
 
I'm sure I've seen your post before, but try this approach - put your
start-period date in D1, your end-period date in E1 and this formula
in F1:

=SUMPRODUCT((A$1:A$100>=D$1)*(A$1:A$100>=E$1)*(B$1:B$100>A$1:A$100)*(C
$1:C$100))

This checks to see if the dates in A fall within the start-period and
end-period, and that the date in B is larger than A, and adds column C
if those conditions are met. I've assumed you have 100 rows, so change
this if you have more.

Hope this helps.

Pete
 
i've done a variation of this. i actually need to add in a value and can't
get the syntax correct...

=SUMPRODUCT(--(H3:H200="ABC")+(H3:H200="ABCD"))*(C$3:C$100>DATE(2008,5,18))*(C$3:C$100<=DATE(2008,5,22))*(D$3:D$100>C$3:C$100)*(E$3:E$100)

i forgot, i need to base this calculation based on a field in column H.
thanks again.
 
Did you mean <=E$1 rather than >=E$1 ?
--
David Biddulph

I'm sure I've seen your post before, but try this approach - put your
start-period date in D1, your end-period date in E1 and this formula
in F1:

=SUMPRODUCT((A$1:A$100>=D$1)*(A$1:A$100>=E$1)*(B$1:B$100>A$1:A$100)*(C
$1:C$100))

This checks to see if the dates in A fall within the start-period and
end-period, and that the date in B is larger than A, and adds column C
if those conditions are met. I've assumed you have 100 rows, so change
this if you have more.

Hope this helps.

Pete
 
Yeah, you're right, David - my mistake, though the OP seems to have
got her answer from another post.

Pete
 
Well, you've introduced things in columns D and E as well as H, so I'm
not really sure what you are doing now. However, if you use the +
symbol in this type of formula it will be taken as OR. So if your
latest condition is for column C to be ABCD or ABC then you have
missed a bracket after the -- as well as from the end of the formula.
Also, your ranges were different for column H. I think it should be:

=SUMPRODUCT(((H$3:H$100="ABC")+(H$3:H$100="ABCD"))*(C$3:C
$100>DATE(2008,5,18))­*(C$3:C$100<=DATE(2008,5,22))*(D$3:D$100>C$3:C
$100)*(E$3:E$100))

Hope this helps.

Pete
 
thanks guys, i've gotten the correct formula based on the below. now, here's
a modification: when i add another variable in the beginning, i can't get
the correct syntax...

=SUMPRODUCT(--(H3:H200="ABC")+(H3:H200="ABCD"))*(C$3:C$100>DATE(2008,5,18))*(C$3:C$100<=DATE(2008,5,22))*(D$3:D$100>C$3:C$100)*(E$3:E$100)

slight modification, i want the sum the qty of what's in those rows based on
the dates.
 
Well why don't you start by looking at your parentheses and ensure that
you've got matching pairs in appropriate places for the syntax of the
SUMPRODUCT function?
 
See my reply to your earlier request on this in this same thread -
does that do it for you?

Pete
 
thanks so much guys!!

David Biddulph said:
Well why don't you start by looking at your parentheses and ensure that
you've got matching pairs in appropriate places for the syntax of the
SUMPRODUCT function?
--
David Biddulph

Ann said:
thanks guys, i've gotten the correct formula based on the below. now,
here's
a modification: when i add another variable in the beginning, i can't get
the correct syntax...

=SUMPRODUCT(--(H3:H200="ABC")+(H3:H200="ABCD"))*(C$3:C$100>DATE(2008,5,18))*(C$3:C$100<=DATE(2008,5,22))*(D$3:D$100>C$3:C$100)*(E$3:E$100)

slight modification, i want the sum the qty of what's in those rows based
on
the dates.





Pete_UK said:
Yeah, you're right, David - my mistake, though the OP seems to have
got her answer from another post.

Pete

On May 29, 5:09 pm, "David Biddulph" <groups [at] biddulph.org.uk>
wrote:
Did you mean <=E$1 rather than >=E$1 ?
--
David Biddulph


I'm sure I've seen your post before, but try this approach - put your
start-period date in D1, your end-period date in E1 and this formula
in F1:

=SUMPRODUCT((A$1:A$100>=D$1)*(A$1:A$100>=E$1)*(B$1:B$100>A$1:A$100)*(C
$1:C$100))

This checks to see if the dates in A fall within the start-period and
end-period, and that the date in B is larger than A, and adds column C
if those conditions are met. I've assumed you have 100 rows, so change
this if you have more.

Hope this helps.

Pete




hi, i'm trying to look in column A for a date that falls within a
specific
time frame, then look in column B to see if that date is > than the
date
in
column A, if it is, then add the qty in column C.
ex:
A1: 5/22/08 B1: 5/23/08 C1: 4
A2: 5/23/08 B2: 5/23/08 C2: 2

so if formula is look in col A for all dates within 5/19 and 5/22,
then i
want the result to show 4.
tia- Hide quoted text -

- Show quoted text -
Click to expand...
Click to expand...
Click to expand...
 
Back
Top