Home |
Search |
Today's Posts |
#1
|
|||
|
|||
30, 60, 90 days late and due within 14 days
I am working on a spreadsheet that will calculate late suspenses. Basically
I have 3 different types of late suspenses and am looking to calculate between 0 and 30 days late, 30 - 60 days late, 60 - 90 days late and over 90 days and each of these time frames for the three categories. Here is what my spread sheet looks like: Name ID TYPE SUSP DAYS OVER DUE UNIT WOOD 6470 N/A 18-Mar-05 (234) A SMITH 7453 UNIT 22-APR-05 (199) B JONES 9741 CO 1-Nov-05 (6) C Here is the formula I am trying to use: =SUMPRODUCT(--(DATA!C2:C319="UNIT"),--(DATA!D2:D319=TODAY()+30)) |
#2
|
|||
|
|||
30, 60, 90 days late and due within 14 days
Are you trying to count how many in each band? If so, then try
=SUMPRODUCT(--(DATA!C2:C319="UNIT"),--(DATA!D2:D319<(TODAY()-90))) =SUMPRODUCT(--(DATA!C2:C319="UNIT"),--(DATA!D2:D319=(TODAY()-90))), --(DATA !D2:D319<(TODAY()-60))) etc. -- HTH RP (remove nothere from the email address if mailing direct) "armyaviatr" wrote in message ... I am working on a spreadsheet that will calculate late suspenses. Basically I have 3 different types of late suspenses and am looking to calculate between 0 and 30 days late, 30 - 60 days late, 60 - 90 days late and over 90 days and each of these time frames for the three categories. Here is what my spread sheet looks like: Name ID TYPE SUSP DAYS OVER DUE UNIT WOOD 6470 N/A 18-Mar-05 (234) A SMITH 7453 UNIT 22-APR-05 (199) B JONES 9741 CO 1-Nov-05 (6) C Here is the formula I am trying to use: =SUMPRODUCT(--(DATA!C2:C319="UNIT"),--(DATA!D2:D319=TODAY()+30)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|