orerockon
asked on
Check if date is between 2 dates
I see a solution for this question using visual basic, however I am looking to use it in an Excel function. In the example attached I want to sum a range of values if the date is between two other dates.
I know I can sum if the date is greater or less than a specified date:
=SUMIF($A2:$A4,"<39814",C$ 2:C$4) where the criteria is the Excel date as a serial number (in this case, before 1/1/09).
How can I do this if I want to sumif the date is between two dates? I tried this:
=SUMIF($A2:$A4,AND("<39814 9",">=3944 8"),B$2:B$ 4) where the criteria is supposed to be any date in 2008.
I get a result of 0.
Also, I want to reference cells containing the date, not the Excel date serial value. See the attached example.
Sumif-example.xls
I know I can sum if the date is greater or less than a specified date:
=SUMIF($A2:$A4,"<39814",C$
How can I do this if I want to sumif the date is between two dates? I tried this:
=SUMIF($A2:$A4,AND("<39814
I get a result of 0.
Also, I want to reference cells containing the date, not the Excel date serial value. See the attached example.
Sumif-example.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
or from excel version 2003 you could also use
=SUMIFS(B2:B6;A2:A6;"<4017 9";A2:A6;" >39813")
to get the total for 2009
=SUMIFS(B2:B6;A2:A6;"<4017
to get the total for 2009
Or if you really like sumif
=SUMIF(A2:A6,"<="&$A$5,B2: B6)-SUMIF( A2:A6,"<"& $A$4,B2:B6 )
=SUMIF(A2:A6,"<="&$A$5,B2:
Plug this formula into your cell
=SUM(IF(AND(A2>=A15,A2<A16 ),B2,0),IF (AND(A3>=A 15,A3<A16) ,B3,0),IF( AND(A4>=A1 5,A4<A16), B4,0),IF(A ND(A5>=A15 ,A5<A16),B 5,0),IF(AN D(A6>=A15, A6<A16),B6 ,0))
The long-winded way.
=SUM(IF(AND(A2>=A15,A2<A16
The long-winded way.
ASKER
Exactly what I was looking for thanks!
=SUMPRODUCT((A3:A6>=A4)*(A
regards, barry