How to chart how many employees hired by month and year

[dragonbonetattoo]

I have a series of information I need to display as a chart. B5:B422 are the employee names (employee1, etc.) D5:D422 have the dates hired. ( 01/15/2004). How do i create a chart that shows how many employees where hired in each month of each year? ( how many hired in January, 2004, for example).

[mrice]

Suggest that you use

= DATEVALUE( MONTH(A1)& "/"& "01/" & YEAR(A1))

to force all hires to the first of the month and use a pivot table/chart to display.

[dragonbonetattoo]

I used the following formula:
=DATEVALUE(MONTH(D5:D422)&"/"&"01/"&YEAR(D5:D422))
and it just returned a date. I need it to count how many employees are hired by month and year. Maybe I didn't phrase my question correctly.

[sebak]

Hi,

1. First create pivot table basing on your data.
2. Put hiring data as row lable and employee id or name as value.
3. Then select column with date (just simply select any cell in column with date), right click and select Group
4. In the group box select month and year
5. Voila! Your data is grouped by year and month
6. In order to create chart, select pivot table and just pick type chart from Insert tab

You can find results in selected file. For more info about grouping in pivot tabels refer to below link:
http://chandoo.org/wp/2009/11/17/gro...-pivot-tables/

Hope this helps. Let me know if you have any problems.

Kind regards,
Sebastian

[dragonbonetattoo]

Stupid question-How do I get grouping to work? I selected the grange, right clicked on it and clicked "group." It just gave me a new group column with nothing in it. How do I specify dates in the group field? Thanks.

[sebak]

Could you attach your file, please?

[dragonbonetattoo]

The pivot chart in question is on the Pivot Chart tab. It references the "staff" tab.
Thanks!

[oeldere]

@Sebak

Nice job, nice explaination.

@draggonbonetattoo

get in the pivot table => choose right mouse clik => group (in my version it's the 8 option).

[dragonbonetattoo]

@oeldere
I have tried the right click option to group the dates, and it just gives me a new empty group column. Up at the top under "group" "unGroup', the group field is greyed out. I'm lost..

[oeldere]

See the attached file.

Instead of the perfect solution of Sebak you can also use helpcolumn to determine your year / month etc.

After that you can use pivot table. (in this case on another worksheet).