Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Trying to capture certain dates

  1. #1
    Board Regular kojak43's Avatar
    Join Date
    Feb 2002
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,033
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,496
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default

    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. #4
    Board Regular kojak43's Avatar
    Join Date
    Feb 2002
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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