Question about Date Range

[shdawson]

Hi,


Have the following formula:

=SUMPRODUCT(--('Project Task'!A5:A$60000='Release Chart - CALCUATIONS'!A5),--('Project Task'!I5:I$60000="PRJ"),--('Project Task'!J5:J$60000="PRJ"),--('Project Task'!L5:L$60000<='Release Chart - LOG'!J$3),--('Project Task'!M5:M$60000>='Release Chart - LOG'!J$4))

'Release Chart - LOG'!J$3 = 6/1/2011 12:00:00 AM
'Release Chart - LOG'!J$4 = 6/30/2011 11:59:59 PM


Here is my problem. I have a project that starts on 6/7/2011. So, my count is off by 1.

Clearly, the logic is not correct on the date consideration.

I need to count the number of unique entires in 'Project Task'!A5:A$60000 that have any presence in the month of June, for 'Release Chart - CALCUATIONS'!A5.

Rather stuck on the date consideration. Not sure what to do. Please advise.

Thank you very much.


Kindly,
SHD

 

[Desu Nota from Columbus]

you have your < and > reversed I believe. you want all values greater than or equal to June 1st at 12:00 AM, and vice-versa for june 30th

=SUMPRODUCT(--('Project Task'!A5:A$60000='Release Chart - CALCUATIONS'!A5),--('Project Task'!I5:I$60000="PRJ"),--('Project Task'!J5:J$60000="PRJ"),--('Project Task'!L5:L$60000>='Release Chart - LOG'!J$3),--('Project Task'!M5:M$60000<='Release Chart - LOG'!J$4))</pre>

try this instead

 

[shdawson]

Hi,


Thanks for the posting.

I did what you asked originally, and came up with 0.

Lots of these projects start before 6/1 and end after 6/31.

Hmmmmm......


Not sure if this can be accomplished in SUMPRODUCT.



???
SHD

 

[Desu Nota from Columbus]

Hi,


Have the following formula:

I need to count the number of unique entires in 'Project Task'!A5:A$60000 that have any presence in the month of June, for 'Release Chart - CALCUATIONS'!A5.

I thought you wanted to search June.

 

[shdawson]

Right.

A presence in June, but does not have to occupy all days of the month.

Hmmmm......it really seems like the answer is staring me in the face. But, I do not know it. .



S

 

[shdawson]

Is SUMPRODUCT the correct formula to use here?

 

[markmzz]

If I understood correctly, the formula suggested for Desu Nota from Columbus is correct.

Look at this:

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH><TH>M</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ffff00">TOTAL</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">3</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ffff00">RC</TD><TD style="BACKGROUND-COLOR: #ffff00">DTI</TD><TD style="BACKGROUND-COLOR: #ffff00">DTF</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="COLOR: #ff0000; FONT-WEIGHT: bold">FILTER</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">01/06/2011 00:00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">30/06/2011 23:59</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Col01</TD><TD>Col02</TD><TD>Col03</TD><TD>Col04</TD><TD>Col05</TD><TD>Col06</TD><TD>Col07</TD><TD>Col08</TD><TD>Col09</TD><TD>Col10</TD><TD>Col11</TD><TD>Col12</TD><TD>Col13</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1458</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">01/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">18/06/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4623</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">10/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">28/06/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4741</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">07/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="BACKGROUND-COLOR: #ffff00">PRJ</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">15/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">26/06/2011</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>L1</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--(A5:A$6000='Release Chart - CALCUATIONS'!A5),--(I5:I$6000="PRJ"),--(J5:J$6000="PRJ"),--(L5:L$6000>='Release Chart - CALCUATIONS'!J3),--(M5:M$6000<='Release Chart - CALCUATIONS'!J4))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Markmzz

 

[T. Valko]

Hi,


Have the following formula:

=SUMPRODUCT(--('Project Task'!A5:A$60000='Release Chart - CALCUATIONS'!A5),--('Project Task'!I5:I$60000="PRJ"),--('Project Task'!J5:J$60000="PRJ"),--('Project Task'!L5:L$60000<='Release Chart - LOG'!J$3),--('Project Task'!M5:M$60000>='Release Chart - LOG'!J$4))

'Release Chart - LOG'!J$3 = 6/1/2011 12:00:00 AM
'Release Chart - LOG'!J$4 = 6/30/2011 11:59:59 PM


Here is my problem. I have a project that starts on 6/7/2011. So, my count is off by 1.

Clearly, the logic is not correct on the date consideration.

I need to count the number of unique entires in 'Project Task'!A5:A$60000 that have any presence in the month of June, for 'Release Chart - CALCUATIONS'!A5.

Rather stuck on the date consideration. Not sure what to do. Please advise.

Thank you very much.


Kindly,
SHD

Getting in kind of late here...

If you want to test a range of dates/times to be within the month of June 2011...

--(TEXT(Range,"myyyy")="62011")

 

[shdawson]

Biff,


--(TEXT(Range,"myyyy")="62011")



You seem to have nailed my need. Thank you. I have not 100% tested your formula, but I want to ask a question before you take off for the day.

A question about the "62011" part, please. I have 2 cells setup for my start-date and end-date. How can I reference a cell for the "62011" part? Do I need to setup a 3rd cell? If so, what should the contents of that cell be, please?

Thank you very, very much.


Kindly,
SHD

 

[shdawson]

Hmmmm, this formula is returning an error. Help file speaks to the TEXT function as follows:


TEXT
Converts a value to text in a specific number format.

Syntax
TEXT(value,format_text)


Perhaps I am missing something here. Please let me know how you have used the syntax you provided.


Thanks,
SHD