Reading a Range of Dates

[jamesusaf]

I use Excel 2007 and I am attempting to create a formula that reads gathers data from three different columns to determine sales credit for a particular quarter. Here is what I have so far.

=SUM(IF('[FY13 PS Master Log.xls]PS APPLICANTS'!$B$2:$B$1000=$D$13,IF('[FY13 PS Master Log.xls]PS APPLICANTS'!$L$2:$L$1000="DATE RANGE",IF('[FY13 PS Master Log.xls]PS APPLICANTS'!$K$2:$K$1000="Y",1,0))))

The following formula reads the salespersons ID, then I need it to read a range of dates in the second part(1 Oct 12-31 Oct 12).

Please see the attached file. Thanks for your help! Jim

[jamesusaf]

In my research I was able to learn Excel does not exactly read dates, so I now have the cells reading as general and it converts it to a number. The following formula returns the value I am looking for, but is not complete. What I need is for the formula to evaluate the corresponding numbers from the number that represents 1 Oct 12 to the 31 Oct 12.

IF('[FY13 PS Master Log.xls]PS APPLICANTS'!$B$2:$B$1000=$D$13,IF('[FY13 PS Master Log.xls]PS APPLICANTS'!$T$2:$T$1000>=41183,IF('[FY13 PS Master Log.xls]PS APPLICANTS'!$K$2:$K$1000="Y",1,0)))

41183 represents the number for 1 Oct 2012. So I need the formula to evaluate the cell contents $T$2:$T$1000 to determine if the value is between 1 Oct 2012 and 31 Oct 2012.

[ben_hensel]

I don't 100% understand what I am looking at when I open that spreadsheet, nor do I 100% understand what you are asking for.

Never-the-less, I'm not the kind of guy that can be discouraged from giving his opinion just because he doesn't understand the question!

A:A = dates
B:B = values corresponding to date
C1 = SUMIFS(B:B, A:A,">=10/1/12", A:A,"<=10/31/12")

C1 will deliver the sum of all values in B:B where the corresponding dates fall between 10/1/12 and 10/31/12 (inclusive).

Alternatively,
A1 = date
B1 = AND(A1 >= datevalue("10/1/12"), A1 <= datevalue("10/31/12"))

B1 will return a Boolean (true/false) of TRUE, if the value in A1 is greater-than-or-equal-to Oct 1 2012, AND it is less-than-or-equal-to Oct 31 2012. You can use that to drive an IF or something.