How to create excell chart to reflect attendence rates?

[janiss]

Hi.
I'm totally at lost for creating excell chart : the type i should use and the data configuration
First of all i explain my need:
1) Attendence record for dept by weekly basis but need to show if its not full attendence
2) Attendence record for dept by mthly basis and also reflect emergency leaves and MCs.

I dont know if this is workable but would appreciate any help.
Example of my data
Em No. Dept. Work day
W1 W2 W3 W4
1 Prod 4 5 3 4
2 Prod 4 4 3 4
3 Engine 4 5 3 4
4 Manage4 3 3 4

For instance Employee 2 and 5 in week2 is absent for W2 full attendence is 5 days. And for Prod dept there is employee 1 and 2 so how can the chart reflect it?
Thanks in advance for all the help.


Additional details
MC is medical certification.
I attached a sample data. hope that helps. sorry its a bit confusing. but thanks for all the comments i got.

[teylyn]

Hello,

can you please upload your data sample as an Excel file?

What are MCs? How does the data reflect emergency leave and MCs?

cheers

[Grimace]

Could guess at Medical certificates?

[MarvinP]

Hi janiss,

I agree it is confusing on what you want exactly. See the attached for a short example that may fit. Because you have 2010 Excel it has lots more date filters. You can filter last week, this month or last month. There are many different arrangement of data and possible pivots.

[janiss]

Hi can any1 suggest how to solve this problem of mine?

[teylyn]

Janiss,

Your data is laid out like a report. It is very hard to come up with a report on a report.

If you structure your data input in the way MarvinP has suggested, you can easily create a view on that data as you now have in your file, and you can easily create a pivot table or pivot chart to report on the data.

Create a raw data sheet for your data entry with just a few columns, i.e. Name, Emp. Number, date, attendance type (being work, ABS, MC, PH, etc). Enter each employee's data into that flat table structure. Now you can do all the reporting you want, like a monthly attendance records, slicing and dicing by absence type etc.

If your data structure is bad, you won't be able to get good reports. Get your data structure right from the start and things will be a lot easier.

cheers,