Thanks:  0
Likes:  0

# Thread: Trying to capture certain dates

1. 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.

2. 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.
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"))

3. 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 ]

4. 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"))

[/quote]

Suave! That "Dates" stuff reference is cool.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•