Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Check if date is between 2 dates

Posted on 2011-03-16
Medium Priority
315 Views
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("<398149",">=39448"),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
0
Question by:orerockon
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 50

Expert Comment

ID: 35149031
You can use SUMPRODUCT like this

=SUMPRODUCT((A3:A6>=A4)*(A3:A6<=A5),B3:B6)

regards, barry
0

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 35149079
...obviously you wouldn't normally have the criteria dates as part of the criteria range, that was just by way of example....criteria dates, A4 and A5 should ideally be in different cells. Note that for a whole year only you could use YEAR function like this:

=SUMPRODUCT((YEAR(A3:A6)=2008)+0,B3:B6)

regards, barry
0

LVL 19

Expert Comment

ID: 35149144
or from excel version 2003 you could also use

=SUMIFS(B2:B6;A2:A6;"<40179";A2:A6;">39813")

to get the total for 2009
0

LVL 6

Expert Comment

ID: 35149164
Or if you really like sumif

=SUMIF(A2:A6,"<="&\$A\$5,B2:B6)-SUMIF(A2:A6,"<"&\$A\$4,B2:B6)
0

LVL 1

Expert Comment

ID: 35149782
Plug this formula into your cell
=SUM(IF(AND(A2>=A15,A2<A16),B2,0),IF(AND(A3>=A15,A3<A16),B3,0),IF(AND(A4>=A15,A4<A16),B4,0),IF(AND(A5>=A15,A5<A16),B5,0),IF(AND(A6>=A15,A6<A16),B6,0))

The long-winded way.
0

Author Closing Comment

ID: 35151728
Exactly what I was looking for thanks!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst youâ€™ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to dâ€¦
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
###### Suggested Courses
Course of the Month12 days, 3 hours left to enroll