PIVOT - Number of employees per month

[obionenairobi]

Hello,

I am trying to make a pivot table report based on our employee database via an ODBC query and need the help of some the real experts :-)

In my data I have the following fields:

Company, Department, Name, Date in service, Date out of service

In my pivot I have a start and end date (example 1-1-2015 till 31-12-2015)
Any employee not actively in service will be filtered out in the query (date out of service >= [Start date] or Null)

Now my big challenge:

I would like to build a pivot table which lists all employees by department and show for each month of the year if they were in service

I have attached an Excel showing the data that I get from my database and the pivot which I would like to build

Can someone help me and tell me if it will be possible to create such a pivot if so how?
The problem is that I only have 2 dates per employee; in service and out of service

[David A Coop]

Hello obionenairobi,

I think I understand your requirement!

I have added 12 "Helper Columns" to your DATA sheet, which determine if your employee has been working in each month.

This is the formula:

Please Login or Register  to view this content.

I then created the PT picking up those new columns.

I assumed that you would not want to display "31/12/2999", so using Conditional Formatting in the PT, I made the text white.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[obionenairobi]

I am going to be a pain but do you know if it is possible to have an empty field instead of a zero value?

I have used conditional formatting to have a white font for values of zero but perhaps there is a way to have directly empty fields