SUMPRODUCT Date Ranges

Hi,

I need to note a '0' or '1' within each of the monthly cells in a
table to show resource secured within a given month (the date ranging
from the start and finish date).

So, if the START date is A1 and the END date is B1 and I have a cell
for each month, i.e. C1 = Jan, D1 = Feb and so on till Dec.

As an example, the start date is 06/02/10 and the end date is
08/08/10, how can I show "1" against the Feb to August cells and all
the other cells where they don't fall into the month as "0", i.e. Jan
as "0"?

Many thanks in advance.
0
Nick
5/4/2010 1:06:57 PM
excel 39879 articles. 2 followers. Follow

5 Replies
641 Views

Similar Articles

[PageSpeed] 33

Try

=--(NOT(OR((DATEVALUE("01-"&C1&"-"&YEAR($A$1))<$A$1-DAY($A$1)+1),(DATEVALUE("01-"&C1&"-"&YEAR($B$1))>$B$1-DAY($B$1)+1))))

-- 

HTH

Bob

"Nick" <njflack@hotmail.com> wrote in message 
news:2054a434-b14b-4b90-b562-87c69fb0f7be@a21g2000yqn.googlegroups.com...
> Hi,
>
> I need to note a '0' or '1' within each of the monthly cells in a
> table to show resource secured within a given month (the date ranging
> from the start and finish date).
>
> So, if the START date is A1 and the END date is B1 and I have a cell
> for each month, i.e. C1 = Jan, D1 = Feb and so on till Dec.
>
> As an example, the start date is 06/02/10 and the end date is
> 08/08/10, how can I show "1" against the Feb to August cells and all
> the other cells where they don't fall into the month as "0", i.e. Jan
> as "0"?
>
> Many thanks in advance. 


0
Bob
5/4/2010 1:43:52 PM
"Nick" <njflack@hotmail.com> wrote:
> So, if the START date is A1 and the END date is B1 and
> I have a cell for each month, i.e. C1 = Jan, D1 = Feb
> and so on till Dec.
>
> As an example, the start date is 06/02/10 and the end
> date is 08/08/10, how can I show "1" against the Feb to
> August cells and all the other cells where they don't
> fall into the month as "0", i.e. Jan as "0"?

Write the following formula in C1 and copy across through N1:

=--AND(MONTH($A1)<=COLUMN()-2, COLUMN()-2<=MONTH($B1))


----- original message -----

"Nick" <njflack@hotmail.com> wrote in message 
news:2054a434-b14b-4b90-b562-87c69fb0f7be@a21g2000yqn.googlegroups.com...
> Hi,
>
> I need to note a '0' or '1' within each of the monthly cells in a
> table to show resource secured within a given month (the date ranging
> from the start and finish date).
>
> So, if the START date is A1 and the END date is B1 and I have a cell
> for each month, i.e. C1 = Jan, D1 = Feb and so on till Dec.
>
> As an example, the start date is 06/02/10 and the end date is
> 08/08/10, how can I show "1" against the Feb to August cells and all
> the other cells where they don't fall into the month as "0", i.e. Jan
> as "0"?
>
> Many thanks in advance. 

0
Joe
5/4/2010 3:01:53 PM
PS....

I wrote:
> Write the following formula in C1 and copy across through N1:
> =--AND(MONTH($A1)<=COLUMN()-2, COLUMN()-2<=MONTH($B1))

I would prefer to use row 1 as a title row, putting the months into C1:N1. 
Then use row 2 for the purpose you described, to wit:  A2 and B2 have the 
start and end dates, and C2:N2 contains 1s and 0s.

Put the dates 1/1/2010, 1/2/2010 etc (d/m/yyyy form) into C1:N1, then format 
C1:N1 with Custom mmm; the year and day do not matter.  Then put the 
following formula into C2 and copy across through N2:

=--AND(MONTH($A2)<=MONTH(C$1),MONTH(C$1)<=MONTH($B2))


"Nick" <njflack@hotmail.com> wrote:
> Subject: SUMPRODUCT Date Ranges

I keep wondering why Nick specified SUMPRODUCT in the subject line.  For the 
problem he describes, SUMPRODUCT is not needed.  But I wonder if his 
description is intended to be a paradigm, and he is really asking how to do 
effectively AND using SUMPRODUCT.

In that case, to implement the same logic as above, you might write:

=SUMPRODUCT((MONTH($A2)<=MONTH(C$1))*(MONTH(C$1)<=MONTH($B2)))

Again, this is totally unnecessary, even inappropriate, for the immediate 
problem.  But it might answer the tacit question about how to do similar 
logic using SUMPRODUCT.


----- original message ------

"Joe User" <joeu2004> wrote in message 
news:%23UTq8q56KHA.6052@TK2MSFTNGP02.phx.gbl...
> "Nick" <njflack@hotmail.com> wrote:
>> So, if the START date is A1 and the END date is B1 and
>> I have a cell for each month, i.e. C1 = Jan, D1 = Feb
>> and so on till Dec.
>>
>> As an example, the start date is 06/02/10 and the end
>> date is 08/08/10, how can I show "1" against the Feb to
>> August cells and all the other cells where they don't
>> fall into the month as "0", i.e. Jan as "0"?
>
> Write the following formula in C1 and copy across through N1:
>
> =--AND(MONTH($A1)<=COLUMN()-2, COLUMN()-2<=MONTH($B1))
>
>
> ----- original message -----
>
> "Nick" <njflack@hotmail.com> wrote in message 
> news:2054a434-b14b-4b90-b562-87c69fb0f7be@a21g2000yqn.googlegroups.com...
>> Hi,
>>
>> I need to note a '0' or '1' within each of the monthly cells in a
>> table to show resource secured within a given month (the date ranging
>> from the start and finish date).
>>
>> So, if the START date is A1 and the END date is B1 and I have a cell
>> for each month, i.e. C1 = Jan, D1 = Feb and so on till Dec.
>>
>> As an example, the start date is 06/02/10 and the end date is
>> 08/08/10, how can I show "1" against the Feb to August cells and all
>> the other cells where they don't fall into the month as "0", i.e. Jan
>> as "0"?
>>
>> Many thanks in advance.
> 

0
Joe
5/4/2010 3:47:14 PM
On 4 May, 16:47, "Joe User" <joeu2004> wrote:
> PS....
>
> I wrote:
> > Write the following formula in C1 and copy across through N1:
> > =3D--AND(MONTH($A1)<=3DCOLUMN()-2, COLUMN()-2<=3DMONTH($B1))
>
> I would prefer to use row 1 as a title row, putting the months into C1:N1=
..
> Then use row 2 for the purpose you described, to wit: =A0A2 and B2 have t=
he
> start and end dates, and C2:N2 contains 1s and 0s.
>
> Put the dates 1/1/2010, 1/2/2010 etc (d/m/yyyy form) into C1:N1, then for=
mat
> C1:N1 with Custom mmm; the year and day do not matter. =A0Then put the
> following formula into C2 and copy across through N2:
>
> =3D--AND(MONTH($A2)<=3DMONTH(C$1),MONTH(C$1)<=3DMONTH($B2))
>
> "Nick" <njfl...@hotmail.com> wrote:
> > Subject: SUMPRODUCT Date Ranges
>
> I keep wondering why Nick specified SUMPRODUCT in the subject line. =A0Fo=
r the
> problem he describes, SUMPRODUCT is not needed. =A0But I wonder if his
> description is intended to be a paradigm, and he is really asking how to =
do
> effectively AND using SUMPRODUCT.
>
> In that case, to implement the same logic as above, you might write:
>
> =3DSUMPRODUCT((MONTH($A2)<=3DMONTH(C$1))*(MONTH(C$1)<=3DMONTH($B2)))
>
> Again, this is totally unnecessary, even inappropriate, for the immediate
> problem. =A0But it might answer the tacit question about how to do simila=
r
> logic using SUMPRODUCT.
>
> ----- original message ------
>
> "Joe User" <joeu2004> wrote in message
>
> news:%23UTq8q56KHA.6052@TK2MSFTNGP02.phx.gbl...
>
>
>
> > "Nick" <njfl...@hotmail.com> wrote:
> >> So, if the START date is A1 and the END date is B1 and
> >> I have a cell for each month, i.e. C1 =3D Jan, D1 =3D Feb
> >> and so on till Dec.
>
> >> As an example, the start date is 06/02/10 and the end
> >> date is 08/08/10, how can I show "1" against the Feb to
> >> August cells and all the other cells where they don't
> >> fall into the month as "0", i.e. Jan as "0"?
>
> > Write the following formula in C1 and copy across through N1:
>
> > =3D--AND(MONTH($A1)<=3DCOLUMN()-2, COLUMN()-2<=3DMONTH($B1))
>
> > ----- original message -----
>
> > "Nick" <njfl...@hotmail.com> wrote in message
> >news:2054a434-b14b-4b90-b562-87c69fb0f7be@a21g2000yqn.googlegroups.com..=
..
> >> Hi,
>
> >> I need to note a '0' or '1' within each of the monthly cells in a
> >> table to show resource secured within a given month (the date ranging
> >> from the start and finish date).
>
> >> So, if the START date is A1 and the END date is B1 and I have a cell
> >> for each month, i.e. C1 =3D Jan, D1 =3D Feb and so on till Dec.
>
> >> As an example, the start date is 06/02/10 and the end date is
> >> 08/08/10, how can I show "1" against the Feb to August cells and all
> >> the other cells where they don't fall into the month as "0", i.e. Jan
> >> as "0"?
>
> >> Many thanks in advance.- Hide quoted text -
>
> - Show quoted text -

Thank you both for your advice, much appreciated.

And thank you Joe for your logic around SUMPRODUCT.
0
Nick
5/4/2010 4:12:02 PM
Dear Nick,

You can try in this way -

Mark the Cells as per following

A1=Start Date, B1 = End Date, C1~N1= Jan~Dec, C2....N2 = 1.....12

Put the Start Date and end date values in (A3, B3); (A4,B4) and like wise
downwards,

Your answer values will be obtained from the cells C3~N3, C4~N4, and
likewise downwards

In the cell C3 please copy the formula : - =
IF(AND($A3=0,$B3=0),0,IF(AND(C$2>=MONTH($A3),C$2<=MONTH($B3)),1,0))

Copy the cell and paste formula in other cells in the columns C to N 
starting row 3
downwards.

You will have answers.

Regards,


"Nick" <njflack@hotmail.com> wrote in message 
news:2054a434-b14b-4b90-b562-87c69fb0f7be@a21g2000yqn.googlegroups.com...
> Hi,
>
> I need to note a '0' or '1' within each of the monthly cells in a
> table to show resource secured within a given month (the date ranging
> from the start and finish date).
>
> So, if the START date is A1 and the END date is B1 and I have a cell
> for each month, i.e. C1 = Jan, D1 = Feb and so on till Dec.
>
> As an example, the start date is 06/02/10 and the end date is
> 08/08/10, how can I show "1" against the Feb to August cells and all
> the other cells where they don't fall into the month as "0", i.e. Jan
> as "0"?
>
> Many thanks in advance. 

0
Jayanta
5/6/2010 12:31:38 PM
Reply:

Similar Artilces:

Calculate 1st of month date from existing date.
I want Excel to post a starting date which is 45 days (or other time period) from an initial date. The rules are: (1) If the date is under 15 (middle of the month) set the starting date to the 1st of the month as calculated and (2) if the date is 15 or over, set starting date to the 1st of the next month. For instance, if my starting date is January 1st, 2006 and I want the date 45 days from there, the answer is February 15, 2006. Reset the calculated value of February 15, 2006 to February 1, 2006. If the date was February 16, 2006 (46 days after start), set the date to March 1, 2006. Th...

Sumproduct
Hi all, I should make a condition(AND) in my sumproduct formula with date format(yyyy/mm/dd), why it dosent accept my condition in one column, like this: =SUMPRODUCT(($B$30:$B$3000=$A$1)*($G$30:$G$3000>=$B$1)*($G$30:$G$3000<$B$2)*($J$30:$J$3000)) even I dublicate date column(G) and change the formula to : =SUMPRODUCT(($B$30:$B$3000=$A$1)*($G$30:$G$3000>=$B$1)*($H$30:$H$3000<$B$2)*($J$30:$J$3000)) but it dosen't work again, any idea to solve this problem would be appreciated. Bijan Hi Bijan I don't see anyting wrong with your formula. Instead of multipl...

Excel 2000
How do I sort dates in a column, as no matter what I try nothing chnages in either ascending or descending order. TIA Are you selecting the area where the data is before you try sorting it? "h" <henryn@gri.co.uk> wrote in message news:0d7601c3722f$64ef4e60$a601280a@phx.gbl... : How do I sort dates in a column, as no matter what I try : nothing chnages in either ascending or descending order. : TIA ...

Query for most recent date
I'm trying to pull the most recent versions of data from a history table. Each row contains the item being modified, the date the change was made, what the change was, and which user made it. For each item being modified I want the most recent change, if I only have the itemID and DateChanged columns in the query I can do it by enabling the Total row in the query builder, and set the itemID to groupby and dateChanged to Max. I also want to show the changeTypeID and UserID fields for the change identified by the previous two fields, but none of the options in the Total dropdown appear ...

Electronic Date Stamp
Is there a way to auotmatically insert into an E-Mail the date and/or time it was opened by me? ...

Number of months between two dates?
I can't see a formula that calculates the # of months between two dates. I know that by subtracting the two dates I can get the # of days. Am I overlooking the formula or does anyone know of an easy way to calculate the # of months between two dates? I can conceive of a way that first calculate the number of years, then the number of remaining months, but that is rather cumbersome, I think, so I'm hoping for an easier solution. Thanks hi, =((YEAR(B1-A1)-1900)*12-1)+(MONTH(B1-A1)) format standard -- isabelle isabelle wrote: > hi, > > =((YEAR(B1-A1)-1900)*12-1)+(MO...

Formatting date fields
I could not seem to find the format syntax. I want to format a date field to display '------' when there is no date to print in a record. Can this be done? If no-one has a better suggestion then you can create a second field in your query with iif(IsNull(MyDate),Format(MyDate,"dd/mm/yy"),"----"). Show this field for display purposes and keep the real field for sorting and calculations. Evi "BobC" <Bob.NoSpammm@cox.net> wrote in message news:Kslzj.16736$097.15342@newsfe21.lga... > I could not seem to find the format syntax. > I want to format ...

Sumproduct or??
If I have two columns of numbers: 1 50 2 40 1 20 4 10 3 30 1 50 How do I write a formula that will sum every number in column B that corresponds to a 1 in column A. The answer should be 120. Thanks for any help. I believe you will want to use a sumif() function here if your data starts in A1 then =sumif(A1:A6,1,B1:B6) On Dec 8, 10:19 am, Terry <Terr...@aol.com> wrote: > If I have two columns of numbers: > > 1 50 > 2 40 > 1 20 > 4 10 > 3 30 > 1 50 > > How do I write a formula that will sum every number in column B that > correspon...

Important Dates
Money 2006. No Passport. XP Home SP2. P4 3.0 GHz. 1G RAM. I can't get the Important Dates Item to show up on my Home Page. I shows up on the Customize your Home Page screen, but not on the Home Page itself. Have removed, saved the file and added it back to the Customize screen. Have removed all other items and then Home Page says there are no content on your Home Page. I set up a test date with tomorrow as the selected date, but it still does appear. Is this a bug or am I missing something? -- Peace, BobJ ...

If SUMPRODUCT & Blank cells
I am running Excel 2003 and I am trying to count a range of data for charting. I am using the following formula: '=IF(SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16) *1),SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16)*1),0) It works great....except... In column $M$3:$M$189 there are also blank cells and I want to count these a...

SUMPRODUCT and OR?
How do you count rows from criterias in two columns where the criteri shall be OR? I.e. something lik SUMPRODUCT(('[jisses.xls]Requirements'!$E$2:$E$10000="Car")+('[jisses.xls]Requirements'!$F$2:$F$10000="Car")) but where you get a count on number of rows where either (both column are = Car) or (any of the columns are = Car)? -- Message posted from http://www.ExcelForum.com Rune, Try =SUMPRODUCT(('[jisses.xls]Requirements'!$E$2:$E$10000="Car")+('[jisses.xls]R equirements'!$F$2:$F$10000="Car"))-SUMPRODUCT(('[jisses...

Help Debug Complex Formula (SUMPRODUCT? SUMIF?)
Folks, I'm really struggling with this one. I've got 354 rows of data (rows 3:352). In column G there may be a date. In column M there is a number (1-12) which represents a monthly period, and in column O there is a dollar amount which represents a montly invoice total. I need to construct a formula which calculates the sum of O for a specific period M where there is a date entered (non-blank cell) in G. The formula below is what I constructed but it does not work. Rather it calculates the sum of O for the specified period in M but then multiplies the sum ($16,200) by the num...

How to print in "date received" order
Hi, I'm using Outlook 2003 and I need to print 1000's of emails in "date received" order. I can order them fine using the sort arrow, but when I go to print them they all print out in alphabetical order (I think by "sender"). This is having a huge impact on my job and my helpdesk claim that the facility to print in date received order is not in Outlook 2003 - something I find impossible to believe! Any help would be very gratefully received. Thank you, Simon "simonsmith" <simon.smith@culture.gsi.gov.uk> wrote in message news:1124698993.462014.2...

Date Subtraction #2
Hi I run Win2K with Excel 2K. I would like to enter a date in a cell (eg Oct-05) and have the preceding 11 cells automatically put the previous months in. Example: In cell A15 = Oct-05 (entered) In cell A14 = Sep-05 (automatically) In cell A13 = Aug-05 (automatically) In cell A12 = Jul-05 (automatically) etc etc etc Is there a formula that can do this? Any help will be much appreciated...thanks ! John On Wed, 9 Nov 2005 16:34:57 -0800, "John Calder" <JohnCalder@discussions.microsoft.com> wrote: >Hi > >I run Win2K with Excel 2K. > >I would like to ent...

PO Integration-date format
Hi Folks I'm using ver 10 sp 4 and I need to integrate PO's in a terminal server environment. I've used a 'test' user with the date set to the American format of yyyy/mm/dd the PO will come in just fine. Problem is that we are in South Africa and our date format is dd/mm/yyyy. The PO won't come in against that format. I am using the Excel driver. Does anyone have an idea how I can bring PO's in without the date format being American? Many thanks Sheila Sheila, Please take a look at my article "Supported Date Formats in Integration Manager"...

Transaction dates a day off after daylight savings time
I hope someone can help, I've searched online and cannot find anything. Since the daylight savings time change, the transactions are downloaded as a day earlier. For example, in my online banking the date says 4/2, but when it downloads, it shows 4/1, in which I have to manually change so it can match my acct. The dates are correct in my online banking acct. I'm using Money 2006 deluxe. Please help! In microsoft.public.money, maxine97 wrote: >I hope someone can help, I've searched online and cannot find anything. >Since the daylight savings time change, the transa...

Diff between two dates formatted as years and months
I am trying to display an employee's length of service at a particular date using a simple formula to subtract one date from the other and format the result as y"y" m"m" so that I get eg. 2y 4m as the person's service. It is returning odd results eg. 0y 12m for diff between 1/1/04 and 1/1/05 and 1y 1m for diff between 31/12/03 and 1/1/05. Is there a more accurate method of doing this to ensure that I get the result I want? Thanks for all your help A complete explanation: http://www.cpearson.com/excel/datedif.htm -- Kind Regards, Niek Otten Microsoft ...

Specific Date from a Range
Hello, I have a spreadsheet that has a column of dates that look like this: Activity Dates 2/15/2010 03/01/2010-03/31/2010 03/01/2010-03/31/2010 03/01/2010-03/31/2010 03/01/2010-03/31/2010 03/01/2010-03/31/2010 03/01/2010-03/31/2010 03/01/2010-03/31/2010 03/01/2010-03/31/2010 03/01/2010-03/31/2010 2/28/2010 2/28/2010 2/28/2010 2/28/2010 2/28/2010 03/01/2010-03/31/2010 03/01/2010-03/31/2010 03/01/2010-03/31/2010 2/1/2010 03/01/2010-03/31/2010 03/01/2010-03/31/2010 03/01/2010-03/31/2010 03/01/2010-03/31/2010 03/01/2010-03/31/2010 2/25/2010 03/01/2010-03/31/2010 ...

Sumproduct or ?????
Hi All, I have 2 tables one is cust table and the other one is amount table. A B C D Customer Table Amount Table Parent ID Cust # Cust # Amount 2001 AA AA 5 2001 BB AA 5 2001 CC AA 5 2001 DD AA 5 2001 EE AA -5 2001 FF AA -5 2001 GG BB 3 BB 3 BB -3 Summary AA - I want to be able to count if "AA" in Cust table (Column B) then count positive amount minus negative amount in amount table (Column D). In this case the answer is "2" BB - The same thing with "BB". The answer is "1" Thank you ...

How Can I Convert A (Row, Column) to Range?
For example... how can I do... Row, Column to some form like A55 Thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ "kenji4861" <kenji4861.vy340@excelforum-nospam.com> wrote in message news:kenji4861.vy340@excelforum-nospam.com... > For example... how can I do... > > Row, Column to some form like A55 > Do you mean that you have two numbers representing the row and column numbers of the reference that you want, for example 1 (co...

Adding Dates
I have a field with arrival date then a field with days and another field with departure date. I enter a date in the arrival date field, then I put in how many days as a number in the days field and using a Macro command on exit I want the departure date to show automatically the arrival date plus the days. How do I do it ? On Mon, 28 Jan 2008 19:58:48 +0200, Ange Kappas wrote: > I have a field with arrival date then a field with days and another field > with departure date. I enter a date in the arrival date field, then I put in > how many days as a number in the days fiel...

Open Spreadsheet--Formatting Changed to Date??
I opened my spreadsheet and some, but not all of the columns on every sheet in the workbook has changed to a date format. I opened other spreadsheets and there was no change. Can anyone explain this. It's a real pain to deal with. ...

Range of Cells....
I am currently using a range of cells in a formula...e.g. C15:C20 I want to use multiple ranges of cells....e.g. C15:N15 E20:N20 G15:P20 What I am using is the +SUMPRODUCT formula.... Thanks! Post your formula(s) for comments. sumproduct ranges must be the same size -- Don Guillett SalesAid Software donaldb@281.com "BenJAMMIN" <BenJAMMIN@discussions.microsoft.com> wrote in message news:4E90210B-1E69-4B21-9C92-3608E4481C5C@microsoft.com... > I...

Increasing the speed of Sumproduct
Hi, 1. Just read http://www.mcgimpsey.com/excel/formulae/doubleneg.html In this JEM says that we have double negs so that =SUMPRODUCT(--(A1:A5>10),B1:B5)) can be coerced in to 1. As per JEM"s explanation single unary will coerce True/False to Zero/One and the second double unary is used so that the negative values could be converted to its original sign. My "reasoning" was instead of using double negative sign why not use a single + sign and achieve further speed increase. So I did this =SUMPRODUCT(--(A1:A5>5),--(A1:A5<10),B1:B5) . The data I used was ...

Receipt / Invoice printing Terms & Due date
Good day, We do a lot of charging on customer accounts. Is there a way to show the Terms and the Invoice due date on each invoice / receipt? Thanks in advance, Steve The only way I have been able to accomplish this is by putting the terms into a customer custom text field and printing that on the receipt. We then created a customization that will populate a receipt variable with the current date + the number of days in the terms. I have not been able to use any other receipt variable to get this to show up. Casey Hanson New West Technologies Check out amazing RMS extensions: http:...