Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Calculating current age of employees through using Pivot Table

0

Dear Respected Members,

First of all would like to appreciate you for the wonderful efforts you put on this website and brillant content you share via youtube. Hats Off!!!!!! Totally Amazing!!!

My query is that I have around 300 active employees list where in the management wants to understand certain demographics.

Kindly guide:

1. How can I through using Pivot Table ONLY calculate the current age of all those 300 employees through their date of birth? (NOT using any formula on the original employee database worksheet)

2. How can I through using Pivot Table ONLY calculate the number of year of experience worked till date for all 300 employees through their Date of Joining (NOT using any formula on the original employee database worksheet)

*Remember that I have to present the results in a report format so need to work on Pivot Table ONLY.

I hope such data can easily be presented and worked on Pivot Table

Seeking guidance please.

Regards,

Akash Sharma 

Answer
Discuss

Discussion

Akash

If the names and dates in your "Employee database" sheet are real, please remove the file (since you would most probably be breaking data protection rules) and provide one with "test" data (or anonymous details).

Secondly, if you are to present in a Pivot Table, are you allowed to use Calculated Fields in your Pivot Table? If not, it will be difficult to present the spread of age and experience.
John_Ru (rep: 6092) Nov 20, '20 at 12:43 pm
In addition to what John already said, why do you insist on a pivot table? Why not create a report in the format you want and pull data from the db into it by whatever means (formulas or code)?
Consider that Excel doesn't support "pushing" of data. You can't enter a formula in the db and make a result appear elsewhere. Excel only has the capability of "pulling" data. Therefore any formnula you might consider must always be in the place where its result is shown. This means that your worry about having formulas in your database that serve your report is groundless. Excel can't do it even if one tried.
Variatus (rep: 4889) Nov 20, '20 at 6:47 pm
Add to Discussion

Answers

0
Selected Answer

Akash

In the attached version of your workbook, I've modified ONLY the sheet "Age Factor" and modified the Pivot Table to show (for each employee) DOB, calculated age today (from DOB), joining date and calculated years of expereince today (from joining date).

In each case of the calculated values, I've added a Calculated Field to the pivot table (click in the table and you're see that option under Analyze menu item "Fields, Items, & Sets...").  The formulae used are:

= yearfrac( 'Date of Birth',44156,1)
= yearfrac( 'Date of Joining',44156,1)

where the bold field names above are added to the field formula by the Insert button and 44156 is the numeric value of today's date (21 Nov) since you can't use the TODAY() function in such fields. Note that the Field settings need to be changed to display as a number (not a default as Excel will do by default).

Note you can test the YEARFRAC formula in a normal spreadsheet if you're unfamiliar with it (but replace the 'database field' bit with a date before today).

REVISION 1: Forgot to say above that I'd editted the row and colum labels of the pivot table (so it doesn't show "Max of...") but note that the label name must NOT be the same as a field name. Likewise for the addition below...

In the revised spreadsheet attached, I've added a further pivot table (at the top of "Age Factor" and based on above the table/new fields mentioned below) which probably gives the kind of summary information that managers can digest more easily (!) and without seeing names.

It has age as columns and experience as rows BUT I've used the pivot table Group function to collect the data together (e.g. age less than 25, 25 to 30) so there aren't loads of rows and columns, each with 1 or 2 people i the table). If you click in an axis (A5 for rows say or C2 for columns), then right click, you'll see an item Group... where this can be changed (or use the Ungroup to remove).

I've then added "color scale" conditional formatting to the resultant pivot table so colours highlight were employees fall (e.g. single biggest group is 5.5 to 6 years expereince and age 30 to 35). Pretty hey?

Hope this helps. 

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login