Spreadsheet: 76998d1277455473-calculate-dates-with-conditional-formatting-personnel-rotation-2010
Sheet: Sheet1

 |  | 24 |  |  |  |  |  |  |  |  |  |  |  |  | JAN |  |  |  |  |  |  |  |  |  | FEB |  |  |  |  |  |  |  | MAR |  |  |  |  |  |  |  | APR |  |  |  |  |  |  |  | MAY |  |  |  |  |  |  |  |  |  | JUN |  |  |  |  |  |  |  | JUL |  |  |  |  |  |  |  |  |  | AUG |  |  |  |  |  |  |  | SEP |  |  |  |  |  |  |  | OCT |  |  |  |  |  |  |  |  |  | NOV |  |  |  |  |  |  |  | DEC
NAME | POSITION | IN | OUT | IN | OUT | IN | OUT | IN | OUT | IN | OUT | IN | OUT | WEEKS >>>> | 1 |  | 2 |  | 3 |  | 4 |  | 5 |  | 6 |  | 7 |  | 8 |  | 9 |  | 10 |  | 11 |  | 12 |  | 13 |  | 14 |  | 15 |  | 16 |  | 17 |  | 18 |  | 19 |  | 20 |  | 21 |  | 22 |  | 23 |  | 24 |  | 25 |  | 26 |  | 27 |  | 28 |  | 29 |  | 30 |  | 31 |  | 32 |  | 33 |  | 34 |  | 35 |  | 36 |  | 37 |  | 38 |  | 39 |  | 40 |  | 41 |  | 42 |  | 43 |  | 44 |  | 45 |  | 46 |  | 47 |  | 48 |  | 49 |  | 50 |  | 51 |  | 52
RICKY | Cost Control Engr. | 2010-01-20 00:00:00 | 2010-02-18 00:00:00 | 2010-03-19 00:00:00 | 2010-04-19 00:00:00 | 2010-05-18 00:00:00 | 2010-06-19 00:00:00 | 2010-07-18 00:00:00 | 2010-08-16 00:00:00 | 2010-09-05 00:00:00 | 2010-10-04 00:00:00 | 2010-11-13 00:00:00 | 2010-12-11 00:00:00 | FORMULA>>>> |  |  |  |  |  |  |  | 30 days |  |  |  |  |  |  |  | 30 days |  |  |  |  |  |  |  | 32 days |  |  |  |  |  |  |  |  |  | 30 days |  |  |  |  |  |  |  | 33 days |  |  |  |  |  |  |  | 30 days |  |  |  |  |  |  |  |  | 30 days |  |  |  |  |  |  |  |  | 21 days |  |  |  |  |  | 30 days |  |  |  |  |  |  |  | 41 days |  |  |  |  |  |  |  |  |  | 29 days |  |  |  |  |  |  |  | 21 days
JOEY | Planning Engineer | 2010-05-01 00:00:00 | 2010-07-08 00:00:00 | 2010-08-05 00:00:00 | 2010-10-02 00:00:00 |  |  |  |  |  |  |  |  | FORMULA>>>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 69 days |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 29 days |  |  |  |  |  |  |  | 59 days |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 92 days
[NAME] | [NAME] | 2010-09-12 00:00:00 | 2010-11-21 00:00:00 | 2010-12-15 00:00:00 |  |  |  |  |  |  |  |  |  | FORMULA>>>> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 70 days |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 24 days |  |  |  |  |  |  | 17 days
 |  |  |  |  |  |  |  |  |  |  |  |  |  | FORMULA>>>>
 |  |  |  |  |  |  |  |  |  |  |  |  |  | FORMULA>>>>
 |  |  |  |  |  |  |  |  |  |  |  |  |  | FORMULA>>>>
 |  |  |  |  |  |  |  |  |  |  |  |  |  | FORMULA>>>>
NOTE:
1.  Each Week has 2 columns to separate the in & out if it falls in the same week. (Green/Red)
2.  It should autmatically calculates the days he's in & he's out and highlight the week.  (Green=IN ; Red=OUT)
3.  After Highlighting it should have the #  of days inside each Green/Red.
4.  Merge & Center each Color for easy navigation.
5.  The calculation always ends in Dec. 31 2010 (but should be flexible for next years use).  So if he's IN it should calculate till Dec 31 only, same thing if he's OUT.