Trying to capture certain dates

kojak43 · Apr 17th, 2002, 08:41 AM

With the great help of several people yesterday, I got most of what I want to work. I modified the formulas to show job on a list for 30 days or less and those jobs that have been on the list for more than 90 days.
I use =SUMPRODUCT((DATES<=TODAY()-90)*(STATUS<>"D")) for the over 90 day reference and =SUMPRODUCT((DATES>=TODAY()-30)*(STATUS<>"D"))for the 30 days or fewer formula.

Where I am getting tripped up is that period of time that is 31 days to 89 days.
Any advice?

Aladin Akyurek · Apr 17th, 2002, 09:06 AM

Quote:

On 2002-04-17 07:41, kojak43 wrote:
With the great help of several people yesterday, I got most of what I want to work. I modified the formulas to show job on a list for 30 days or less and those jobs that have been on the list for more than 90 days.
I use =SUMPRODUCT((DATES<=TODAY()-90)*(STATUS<>"D")) for the over 90 day reference and =SUMPRODUCT((DATES>=TODAY()-30)*(STATUS<>"D"))for the 30 days or fewer formula.

Where I am getting tripped up is that period of time that is 31 days to 89 days.
Any advice?

Enter in e.g., F1: =TODAY()

1. LessThan or Equal to 30:

=SUMPRODUCT(($F$1-DATES<=30)*(STATUS<>"D"))

2. Between 30 and 90 exclusive:

=SUMPRODUCT(($F$1-DATES>30)*($F$1-DATES<90)*(STATUS<>"D"))

3. 90 and above:

=SUMPRODUCT(($F$1-DATES>=90)*(STATUS<>"D"))

Do these meet your requirements?

Aladin

Dave Patton · Apr 17th, 2002, 09:21 AM

You could put your criteria in cells and name the cells. You may then find the formulas easier to read.

Today 17/04/2002
30 18/03/2002 name rT_30
90 17/01/2002 name rT_90

Formulas
30 =SUMPRODUCT((DATES>=rT_30)*(STATUS<>"D"))
31 - 89=SUMPRODUCT((DATES>rT_90)*(DATES"D"))
90> =SUMPRODUCT((DATES<=rT_90)*(STATUS<>"D"))

[ This Message was edited by: Dave Patton on 2002-04-17 08:23 ]

kojak43 · Apr 17th, 2002, 09:25 AM

Enter in e.g., F1: =TODAY()

1. LessThan or Equal to 30:

=SUMPRODUCT(($F$1-DATES<=30)*(STATUS<>"D"))

2. Between 30 and 90 exclusive:

=SUMPRODUCT(($F$1-DATES>30)*($F$1-DATES<90)*(STATUS<>"D"))

3. 90 and above:

=SUMPRODUCT(($F$1-DATES>=90)*(STATUS<>"D"))

Do these meet your requirements?

Aladin
[/quote]

Suave! That "Dates" stuff reference is cool.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 17th, 2002, 08:41 AM	#1
kojak43 Board Regular Join Date: Feb 2002 Posts: 270	With the great help of several people yesterday, I got most of what I want to work. I modified the formulas to show job on a list for 30 days or less and those jobs that have been on the list for more than 90 days. I use =SUMPRODUCT((DATES<=TODAY()-90)(STATUS<>"D")) for the over 90 day reference and =SUMPRODUCT((DATES>=TODAY()-30)(STATUS<>"D"))for the 30 days or fewer formula. Where I am getting tripped up is that period of time that is 31 days to 89 days. Any advice?

Apr 17th, 2002, 09:21 AM	#3
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	You could put your criteria in cells and name the cells. You may then find the formulas easier to read. Today 17/04/2002 30 18/03/2002 name rT_30 90 17/01/2002 name rT_90 Formulas 30 =SUMPRODUCT((DATES>=rT_30)(STATUS<>"D")) 31 - 89=SUMPRODUCT((DATES>rT_90)(DATES"D")) 90> =SUMPRODUCT((DATES<=rT_90)*(STATUS<>"D")) [ This Message was edited by: Dave Patton on 2002-04-17 08:23 ]

Apr 17th, 2002, 09:25 AM	#4
kojak43 Board Regular Join Date: Feb 2002 Posts: 270	Enter in e.g., F1: =TODAY() 1. LessThan or Equal to 30: =SUMPRODUCT(($F$1-DATES<=30)(STATUS<>"D")) 2. Between 30 and 90 exclusive:* =SUMPRODUCT(($F$1-DATES>30)($F$1-DATES<90)(STATUS<>"D")) 3. 90 and above: =SUMPRODUCT(($F$1-DATES>=90)*(STATUS<>"D")) Do these meet your requirements? Aladin [/quote] Suave! That "Dates" stuff reference is cool.