Creating a Pivot Table from Received Column

[SouthAfrican]

Hi there,

I am trying to create a Pivot Table for translations of training material-Spanish and Polish based on the Date Uploaded column. By combining all columns with the WORKDAY function. I need to have a Bar graph that shows so many traing materials set as red (Late) green (Complete) and Pending (blue) Sent (Yellow) based on the Received columns.

So if today is 25 Mar-2011:

Columns in excel
K | L | M | N | O |
Date Uploaded|PL Translations |Received? |Spanish Translations received | Received?|
17 Mar-2011 | 24-Mar-2011 red | Late | 24 Mar-2011 (red) | No
21-Mar-2011 |28-Mar-2011 (blank) | Sent |

FORMULA USED in column M:IF (L<TODAY(),"Late","Sent")] it should turn red

Q1.How do I create a Pivot table in MS Excel 2007 for PL Translations based on the M,O (Received column) based on Data sets

Complete=green
Late =red
Pending =blue
Sent = Yellow

Q2. What formula can I used to get it green (complete) and blue for Pending (if no dates were inserted in excel)?

I will appreciate your reply as I am struggling to create this Pivot table.

Thank you