Spreadsheet: 118873d1314066669-excel-2007-multiple-conditions-formula-current-status-worksheet
Sheet: Sheet1

 |  | Today's Date | 2011-08-23 00:00:00 | IF($D$1>(H3+365),"PM2",IF($D$1>(G3+365),"PM1",IF($D$1>(I3+30),"Calib")))
 |  |  | S/N # | Current Status | Red Tagged | PM1 Date | PM2 Date | Calib Date | [NAME]
1 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123376 |  | Red Tagged | 1900-01-01 00:00:00 | 2011-08-18 00:00:00 | 1900-01-01 00:00:00 | 2011-08-18 00:00:00
2 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123377 |  |  | 2010-03-05 00:00:00 | 2010-01-01 00:00:00 | 1900-01-01 00:00:00 | 1900-01-01 00:00:00
3 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123378 |  |  | 2011-05-19 00:00:00 | 1900-01-01 00:00:00 | 1900-01-01 00:00:00 | 1900-01-01 00:00:00
4 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123379 |  |  | 2010-08-01 00:00:00 | 2011-02-05 00:00:00 | 1900-01-01 00:00:00 | 1900-01-01 00:00:00
5 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123380 |  |  | 1900-01-01 00:00:00 | 2011-08-08 00:00:00 | 2011-08-14 00:00:00 | 1900-01-01 00:00:00
6 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123381 |  |  | 2011-08-04 00:00:00 | 2011-08-01 00:00:00 | 1900-01-01 00:00:00 | 1900-01-01 00:00:00
7 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123382 |  |  | 2011-08-15 00:00:00 | 2011-08-15 00:00:00 | 2011-08-16 00:00:00 | 1900-01-01 00:00:00
8 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123383 |  |  | 1900-01-01 00:00:00 | 1900-01-01 00:00:00 | 1900-01-01 00:00:00 | 2011-08-22 00:00:00
9 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123384 |  |  | 2011-07-04 00:00:00 | 1900-01-01 00:00:00 | 1900-01-01 00:00:00 | 2011-08-04 00:00:00
10 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123385 |  |  | 2011-08-01 00:00:00 | 2011-08-01 00:00:00 | 1900-01-01 00:00:00 | 2011-08-20 00:00:00
11 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123386 |  |  | 1900-01-01 00:00:00 | 2011-07-15 00:00:00 | 1900-01-01 00:00:00 | 2011-08-15 00:00:00
12 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123387 |  |  | 2011-07-04 00:00:00 | 1900-01-01 00:00:00 | 2011-08-15 00:00:00 | 2011-08-15 00:00:00
13 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123388 |  |  | 2011-08-15 00:00:00 | 2011-08-15 00:00:00 | 2011-08-16 00:00:00 | 2011-08-17 00:00:00
 | Desired Results |  | S/N # | Current Status | Red Tagged | PM1 Date | PM2 Date | Calib Date | [NAME]
1 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123376 | Red Tagged | Red Tagged | 1900-01-01 00:00:00 | 2011-08-18 00:00:00 | 1900-01-01 00:00:00 | 2011-08-18 00:00:00
2 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123377 | PM2 |  | 2010-03-05 00:00:00 | 2010-01-01 00:00:00 | 1900-01-01 00:00:00 | 1900-01-01 00:00:00
3 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123378 | PM2 |  | 2011-05-19 00:00:00 | 1900-01-01 00:00:00 | 1900-01-01 00:00:00 | 1900-01-01 00:00:00
4 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123379 | PM1 |  | 2010-08-01 00:00:00 | 2011-02-05 00:00:00 | 1900-01-01 00:00:00 | 1900-01-01 00:00:00
5 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123380 | PM1 |  | 1900-01-01 00:00:00 | 2011-08-08 00:00:00 | 2011-08-14 00:00:00 | 1900-01-01 00:00:00
6 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123381 | Calib |  | 2011-08-04 00:00:00 | 2011-08-01 00:00:00 | 1900-01-01 00:00:00 | 1900-01-01 00:00:00
7 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123382 | [NAME] |  | 2011-08-15 00:00:00 | 2011-08-15 00:00:00 | 2011-08-16 00:00:00 | 1900-01-01 00:00:00
8 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123383 | PM1 |  | 1900-01-01 00:00:00 | 1900-01-01 00:00:00 | 1900-01-01 00:00:00 | 2011-08-22 00:00:00
9 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123384 | PM2 |  | 2011-07-04 00:00:00 | 1900-01-01 00:00:00 | 1900-01-01 00:00:00 | 2011-08-04 00:00:00
10 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123385 | Calib |  | 2011-08-01 00:00:00 | 2011-08-01 00:00:00 | 1900-01-01 00:00:00 | 2011-08-20 00:00:00
11 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123386 | PM1 |  | 1900-01-01 00:00:00 | 2011-07-15 00:00:00 | 1900-01-01 00:00:00 | 2011-08-15 00:00:00
12 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123387 | PM2 |  | 2011-07-04 00:00:00 | 1900-01-01 00:00:00 | 2011-08-15 00:00:00 | 2011-08-15 00:00:00
13 | RWCH-C | RWCH-C,RELE WL CA HD,DITS/RDT/INSITE CMP | 11123388 | Ready |  | 2011-08-15 00:00:00 | 2011-08-15 00:00:00 | 2011-08-16 00:00:00 | 2011-08-17 00:00:00
 |  | Truth Table | Word Result | Color Result
 | 1 | Red Tagged (doe not matter what any date cells contain) | Red Tagged | Red
 | 2 | Today's Date > (PM1 Date + 365) and Today's Date > (PM2 Date + 365) and Today's Date > (String Check Date + 365) | PM2 | Red
 | 3 | Today's Date < (PM1 Date + 365) and Today's Date > (PM2 Date + 365) | PM2 | Yellow
 | 4 | Today's Date > (PM1 Date + 365) and Today's Date < (PM2 Date + 365) | PM1 | Yellow
 | 5 | Today's Date > (PM1 Date + 365) and Today's Date < (PM2 Date + 365) and Today's Date < (Calib Date + 30) | PM1 | Yellow
 | 6 | Today's Date < (PM1 Date + 365) and Today's Date < (PM2 Date + 365) and Today's Date > (Calib Date + 30) | Calib | Yellow
 | 7 | Today's Date < (PM1 Date + 365) and Today's Date < (PM2 Date + 365) and Today's Date < (Calib Date + 30) and [NAME] < Calib Date | [NAME] | Yellow
 | 8 | Today's Date > (PM1 Date + 365) and Today's Date > (PM2 Date + 365) and Today's Date < (String Check Date  + 365) | PM1 | Green
 | 9 | Today's Date < (PM1 Date + 365) and Today's Date > (PM2 Date + 365) and Today's Date < (String Check Date  + 365) | PM2 | Green
 | 10 | Today's Date < (PM1 Date + 365) and Today's Date < (PM2 Date + 365) and Today's Date > (Calib Date + 30) and Today's Date < (String Check Date + 365) | Calib | Green
 | 11 | Today's Date > (PM1 Date + 365) and Today's Date < (PM2 Date + 365) and Today's Date > (Calib Date + 30) and Today's Date < (String Check Date + 365) | PM1 | Green
 | 12 | Today's Date < (PM1 Date + 365) and Today's Date >(PM2 Date + 365) and Today's Date < (Calib Date + 30) and [NAME] Date >= (Calib Date) | Ready | Green