Tracking Employee Dates in a dashboard.

[Hntrsmom]

I have these two great worksheets but I want to create a dashboard that will notify me of when a review dates or licenses expiring in 90 days or less and then 60 days or less and then 30 days of less for their annual reviews. Then the same for their quarterly reviews. I have done this with conditional formatting but it would be more efficient if I could build a dashboard so all I would have to do is look at that and it will tell me when which employee's review and license dates are expiring.

I have spreadsheets from multiple years of each the licenses and the reviews dates. I have attached a copy of the licenses and reviews sheet for a reference on what I fill out in each spreadsheet.

I have tried to convert Chandoo's Employee vacation tracker but I am failing miserably at trying to fix it into what I want to use. The basic set up is perfect with the employee vacation tracker dashboard. I just need it to tell me when employee's dates are coming up for expiration.

Can anyone help me??

 

[eibi]

Welcome to the forum!

I'm taking a look at the files you provided, and I have a question about the Performance Review Dates...There are dates in columns F through P -- which of these dates do you want to include on the dashboard? All of them?

Thanks.

 

[Hntrsmom]

I am ultimately looking to have alerts on my dashboard when any of the columns (I,J,K,L,M,N,O,P,Q,R) when any of these dates are coming up on a 90, 60, or 30 day expiration date. Preferably if columns (L,N,P,R) could have an alert that lets me know when the date in that column is 14 days or less.

Thank you so much!

 

[eibi]

I've put together a basic starting point (attached) -- for your feedback, based on the model you referenced...but I just read your most recent post, and I'll have to give that some more thought.

Take a look at the dashboard in the attached file and let me know if it's heading the direction you want to go. We can refine it lots more, but I don't want to go too far down this path unless it's the right direction.

The more specific your feedback, the better.

 

[Hntrsmom]

I looked at your dashboard and it looks great and it looks like we are on the right track. I added comments onto the dashboard you sent me and I will also send you the one I was working on to try to revise myself. The dashboard I modified was Chandoo's employee dashboard but I just added what information I would be looking for if that is helpful at understanding what I was trying to get across.

I appreciate all your efforts.

Thank you!!

 

[eibi]

Kate,

Regarding the Employee Drop-down:

You suggested an employee drop-down like the Chandoo Vacation Tracker template, which requires a little creativity. In Chandoo's original template, the employees all fit on one screen, so the dropdown can highlight the selected employee without requiring the screen to scroll. Your employee list, in contrast, is much too large to fit on one screen. I believe it would require VBA code in order for the screen to scroll to the employee you select, which I can create, but I'm reluctant to do that unless you are comfortable maintaining VBA code...

Instead, I created a dropdown box that duplicates the selected employee's row, along with a flag that indicates the date and type of the next scheduled event for that employee.

See attached. Feedback?

Regarding the Quick Summary:

You suggested a Quick Summary that names employees with upcoming events. I don't think you want this at the top because the length of the list will vary from day to day -- depending on the number of employees who have an event in the next 30, 60 and 90 days. There's no way to predict what the maximum number of names might be in a given period, I hate to block off a large section of empty space at the top of your dashboard...So I suggest putting this information on the side rather than on the top. I've done some of the preliminary data structuring on the Calculations tab, but nothing yet on the Dashboard tab. Maybe tomorrow?

Regarding the Monthly Calendar:

I'll have to save that one for another day.

Regarding a Rolling Calendar:

You mentioned that it would be preferable to set the Gantt chart with rolling months rather than fixed months...It is already set up for you to change the report date in the top left (highlighted cell) to any other date and the dashboard automatically updates. Is this what you meant?

All best.

 

[Hntrsmom]

I do like the drop down box you created. You are right that it would not work at well since I have so many employees to keep track of fence the need of this wonderful dashboard you are helping me out with. I do not think I would be comfortable enough to maintain a VBA quite yet. I see myself having more training in the future with that one.

Regarding the quick summary. I understand what your saying about having that feature on the side of the dashboard. Which is a great idea and I believe that would benefit greatly for the need I am trying to achieve.

With the rolling calendar, what you stated above is exactly what I meant when I was talking about a rolling calendar. I just didn't want to have to keep updating it every 3 months or so if it was a fixed calendar set up.

I appreciate all your help with this project. I fore see others being able to use this template with their employees as well!

Thank you!!! And hopefully hear from you soon.

 

[eibi]

I didn't have time to work anything up yesterday, but I was able to get back to it today for a little bit...

See attached. I've got the "Quick Summary" on the right side of the dashboard (although it isn't very quick -- there are 71 items in the next 90 days...)

Functionally, it achieves what you asked -- though I'm not sure it's quite what you had envisioned?

The last item you suggested is a monthly calendar, and I'm a little bit stumped on that one...I'll give it some thought; there are several different issues at hand to fit it on the dashboard and make it useful -- and as far as I can tell, your chief needs are met by the spreadsheet in its current format.

It will continue to be a project I keep on hand for a while, but I'm certain that I won't come back to it until next week.

Have a good weekend.

 

[Hntrsmom]

I think that looks great! We might not need a calendar mode in there if the quick summary achieves the same basic idea. Now would this be user friendly to add the data into?

 

[eibi]

I've made one more round of modifications in order to accommodate the addition of new employees. I think you now have the capacity for ~107 total employees (and ~245 licenses) in the attached spreadsheet. If you want to add or change an employee's name, go to the employee list tab and make your changes there.

If the information you provided in your initial post is real data, all you need to do is replace the fictional names I created with the names that actually correspond to each employee ID.

Any other data, I think will be as easy as copy and pasting into the Licenses and Certifications Tab or the Performance Review Dates Tab that live in the spreadsheet...these are exact copies of the files you provided in your first post.

See attached.

 

[Hntrsmom]

Thank you so much for all your help. This is fantastic! I would just like to clarify... that the data that is shown on the dashboard is directly from the Performance Review & Licenses sheets? So if I were to change dates on those 2 worksheets it will change on the dashboard as well? Do I have to keep up with the calculations sheet as well? I have updated all the employees that I have been using to the employee table as you directed above.

 

[eibi]

Right-o! That's the best part of a true dashboard -- it automatically updates from the source data you maintain.

The Calculations sheet is simply an intermediate sheet where the data is crunched before it hits the Dashboard. That way, the important formulas don't live on the Dashboard where they might get accidentally edited or deleted. The only time you would need to edit the Calculations tab is if you find an error on the Dashboard or if you want to change the structure of the data on the Dashboard.

As long as you keep the Performance Review and License sheets up to date, the dashboard will update itself appropriately. You can make all your edits on the Performance Review and License sheets, and those dates will be automatically picked up on the Dashboard.

I hope it works great for you!

 

[Hntrsmom]

That's amazing! Thank you so much!!

 

[Hntrsmom]

I actually have another issue with another dashboard if your up to the challenge. I feel like it would be a great deal easier. It has to do with Medical Records Reviews. I am trying to go from one great spreadsheet of data to just gathering the percentages from data.

 

[eibi]

I'm glad to give it some thought, here are a few suggestions:

1. Start it in a new thread on the Dashboard forum. It makes the forum more useful to future users if we keep each topic in a separate thread.

2. See if you can find a reference (sample) format that you want your dashboard to follow...like you did by referencing Chandoo's Vacation Tracker when you started this post. If you can't find a good fit, then draw up an imaginary dashboard with magic markers and scan it in. The more specific the better. If your desired outcome is vague or confusing, it is very difficult to help (see some of the other posts on the board for excellent examples of vague or confusing requests).

3. Provide a data source file that is as complete as possible without compromising the sensitivity of your information. It is important to see the actual source data in the format that you will be maintaining it.

 

[Hntrsmom]

Would there be a way in this file to change where it has the event title? For example change on the side in the upcoming events by Date table. To change the event type to the corresponding column name on the Performance Review sheet. To say if the expiring date was in the 1st Quarter column to have it show that on the dashboard?

 

[eibi]

Sorry about my delayed response -- personal and work obligations prevented me from giving this the attention as soon as I wanted to...

Your request is taking some reworking of the formatting and calculations; I've decided to break the data sets into three groups (Licenses, Annual Reviews, and Quarterly Reviews) rather than just the two original sets (Licenses and Reviews). There is more explaining to do about the reasoning behind that, but I'll save it for another time.

The data restructuring is done, there are just handful of formulas to be re-written in order to get the column headers you requested. See attached the current version.

More to come soon, I hope.

 

[Hntrsmom]

Thank you very much for the time you are taking with me to get this worked out. It is so much easier to be able to look at the dashboard and be able to separate the annual and quarterly reviews since it is a completely different process that I have to do for them. Also, would there by a way to show for example in columns H, J, L, N, P, and R. When those dates are coming up on the dashboard. Right now I have conditional formatting done on the spreadsheet to tell me when I don't have any review scheduled 90 days or less it will turn yellow. Is there anyway to reflect that on the dashboard?

I know you said the data restructuring is done, but you still needed to work on the column headers. Will I still be able to add the information to the spreadsheets in order to keep up with the dashboard.

 

[eibi]

Hello again --

I'm working on passing the column headers through -- and I've got it working for the Quarterly Reviews.

I'm having a problem passing through the column headers from Columns F - J through -- and here's the the rub -- some people have the same date in more than one column. For example, ID 13666 (on Row 6 of the Review Dates Tab) has the same date in Column G and Column I.

In order to develop a solution that makes sense in the long run, I need to create subgroups where the duplicate dates won't happen. But I can't do that without understanding how columns FGHIJ relate to one another:

Can I safely assume that a given employee ID will never have duplicate dates in columns FGH?

Similarly, can I assume that a given employee ID will never have duplicate dates in columns IJ?

For now, the updated file is attached.

You asked about adding information to the spreadsheets -- that hasn't changed, you can still do that as previously confirmed without any problems.

 

[eibi]

Note that when you simply enter a month abbreviation (like Oct or Dec) in your table, Excel treats this as a text string instead of recognizing it as a date, so it doesn't show up on the Dashboard. You'll actually have to assign a date in order for it to get passed through.

Also note -- when you set up a date without a visible year (Jan-26), Excel assumes the current year (2015) to make it a complete date. And past dates won't show up on the Dashboard. For example, if you type 01/01/2015 in the date field on the dashboard, you'll see a full battery of all the quarterly reviews from the past winter. But if you type 01/01/2016, they all disappear because they are all past dates.

In order for this dashboard to work in the long term, you'll either have to go through and update these dates every year -- or you'll have to replace the "hard" coded dates with formulas that automatically add a year when each date passes...

Just a thought as we approach the last 120 days of the year...

 

[Hntrsmom]

Thank you Eibi for your help. With regards for columns F-J. I will try and break down the logic behind those columns.

• F- Annual Date: This date is of the employee's annual review which is ongoing and goes from year to year.
• G- Review Due: This date is used as a guide to let us know that the employee's annual review should be scheduled around this date roughly 38 days before their actual annual review date.
• H- Reports Date: This date is used as a guide to let us know that the employee's annual paperwork should be collected and reports run 30 days before the review due date in column G.
• I- Review Date: This is the actual booked review date that I have scheduled for the employee and manager to meet for the employee's annual review.
• J- PPW to Mgr: This is just a reference date to write down the actual date I have collected and gathered the paperwork and actually sent it to the manager.
• Also, the N/A in columns K-R are there because the managers have requested to not due a review for those dates. Will that interfere with the spreadsheet at all?

In a nutshell... Column G coincides with column I and Column H coincides with column J.
Those dates could potentially be the same. If the date is G is telling me that I should booked the employees review on Sept 2nd. I might actually book that review for Sept 2nd.

I hope that helps and clears up some confusion. Looking a head though. I will go in and make sure all dates have a year at the end of them. I plan on just having this as an ongoing dashboard. I do not want it to be stagnant year by year.

 

[Hntrsmom]

Also if you see any ways to possible make this leaner or a similar processes from your point of view. I would be all ears.

I just need a dashboard to be able to let me know when a quarterly, annual license is coming due. Also when I need to be notified to do the following acts:

• Book Annual Reviews if they have not been booked yet
• Book quarterly reviews if they have not been booked yet
• When licenses are coming close to expiration
• When to send any reports/ paperwork to the managers

 

[eibi]

I was able to pass the column headers through to the dashboard as you requested but at the same time, I omitted Columns I and J because the duplicate dates are a problem I don't have a solution for at this time.

See attached.

I think you'll still be able to add and edit employee information as outlined previously. Entering text (such as NA) in the date cells won't create any problems -- but I would recommend that you avoid entering numbers because Excel will confuse them for dates...

We're reaching the limits of what we'll be able to do with the data in their current format. There could certainly be more developed, but the complexity of the supporting calculations is becoming fairly unwieldy...If you want to advance to much more detail, I agree with your suggestion that it will require a leaner structure.

All best!

 

[lomer]

Hi eibi...I'm in the same situation with hrtrsmom7. i downloaded the last hntrsmom7.xlsx to use the same report for my daily tasks. i need the same things like him but the last sheet with performance doesn't apply to my needs but i cannot delete because all the formulas will have errors. do you think that will be possible to have the file without that sheet?

The important thing for me is to display the LicenseExpDate and the License Code on dashboard.

Thx a lot for your help!

 

[eibi]

lomer,

Welcome to the forums!

I appreciate that you sent me a message about this post because I hadn't been watching this thread.

See attached, with the revisions you requested in your post -- as best as I have understood them. Note that the table on the right side of the dashboard provides the License Name, Location, and Expiration Date you needed.

If a single person has multiple licenses expiring on the same date, it will alert you -- but you'll have to go look those up manually; they don't pass through to the dashboard.

Let me know how it works!

All best.