Tag Archives: HR

Excel Employee Timesheet – from DataScopic.net

Thank you to XLCalibre for the generous opportunity to Guest Blog. I like what’s happening on this website with the interesting real world questions, challenges and solutions. Very different from my site www.DataScopic.net where I focus on concepts and simpler tips.

Today, I’m going to share a recent challenge.

Problem:

Client is a small business and the Office Manager is taking a lot of time processing payroll. She receives papers from the employees … a lot of the writing is illegible. Their math doesn’t add up. She has to hunt them down to get details. It was a long, painful process.

Request:

A timesheet that will have restrictions, required fields and is easy to import into Access.

Result:

Payroll processing dropped from 6 hours to 90 easy minutes.

Specific Need in the document: Detect Overlapping Times

Let’s say

  • Line 3 says that I was on break from noon to 1PM, and
  • Line 6 says that I was working with Dr. Phibes from 12:45PM to 3PM

OOPS! The spreadsheet needs to point this out before it’s submitted to the Office Manager.

Let’s dig into the solution

In building features like this, it helps to

  • Anticipate problems, exceptions, pitfalls, etc. from the user’s perspective.
  • Anticipate problems, exceptions, pitfalls, etc in the solution I create.
  • See if there are already solutions in the world

Answers

  • Anticipation of needs
    • If a user keys in an overlapping time, how will they know?
    • How will they know not only that the new time overlaps but which previous entry does it overlap with?
    • If a user forgot to enter something that happened earlier in the day, can they add it to the bottom of the timesheet or must it be entered in proper sequence?
    • If the user has to put the late entry in sequence, and move a lot of data out of the way, they are going to hate my guts!
    • Anticipate problems, exceptions, pitfalls, etc in the solution I create.
      • VBA Code? I minimize use of VBA code because a lot of people still don’t know VBA exists or they don’t understand it or they’re scared of it. So, in the event that I am hit by a bus or retire and join a rock band, it should be easy to find someone to troubleshoot a non-VBA solution.
      • Did I find existing solutions?
        • I found solutions that would detect overlapping times only if they’re entered sequentially. That’s the Hate Oz’s Guts Solution

 

Key Detail:

The timesheet has room for 45 entries. Thus, I had to generate a solution such that every line is checking itself against the 44 other lines.

First, use columns Q and R as helper columns

NOTE: troubleshooting this formula lead me to using Word because there were so many pieces to manage. See my blog entry on using Word to troubleshoot a formula.

=IF(O7=””,””,IF(AND(OR(R7<=Q8,Q7>=R8),OR(R7<=Q9,Q7>=R9),OR(Q7>=R10,R7

What does the formula say?

  1. Formula trigger: IF O7 is empty, don’t do anything
  2. Test: Comparing line 3 against line 1

Is the Start Time in Line 3 Greater than the End Time on Line 1? 6:01PM is greater than 5:19PM YES

Is the End Time on Line 3 greater than the Start Time on Line 1? 6:06PM is greater than 5:08PM YES

  1. If all is well, “EXCELLENT!” If not, “NOPE”

There are 4 possibilities:

NO OVERLAP

OVERLAP

YES YES

NO YES

NO NO

YES NO

Once the formula is created, for the first entry, just copy it on down the column

Notice: entries 3 and 5 overlap

There is still one problem.

Remember that every line has to check itself against every other line?

When you copy the formula down the column, Entry 2 isn’t checking itself against Entry 1. Entry 3 isn’t checking itself against 1 and 2, so on sand so on until Entry 45 isn’t checking itself against anything.

 

How do we modify this formula? WE DON’T!

This is already complicated enough. Let’s just copy the times down such that

Q57 = Q7 since Q7 is the Start Time of the first entry. Thus, Entry #2 is checking itself against Entry #1 via Q57:R57

There it is! The client is very happy with the document and other features within, and they’ve got a lot more time to focus on their core business.

If you have other solutions, please do share.

 

A big thank you to Datascopic.net for agreeing to guest blog on XLCalibre.com. We hope you enjoyed it!

HR Dashboard Organisation Heirarchy Pyramid Chart

This chart will quickly become a regular feature on your HR dashboard or Balanced Scorecard:

[showmyads]

The pyramid shape is useful to illustrate the relative numbers of employees at each level in your organisation. I’ve seen HR Manager’s use it before to compare different departments and come up with organisational development plans.

So here it is:

HR Dashboard Organisation Heirarchy Pyramid Chart

I’ve seen colleagues struggle to format the chart to look like this, and I had previously used helper cells to include formulas to get the ranges for the chart right. However this version relies upon named ranges to do the work (using OFFSET to find the size of your table), so you only have to look at the real data, and its got conditional formatting to keep the table formatted in our snazzy XLCalibre colours. Formatting the data as a table (which was my initial intention) would make things expand automatically and keep the colours, but it kept throwing up error messages so I went with the OFFSET approach instead.

[showmyads]

If you’re looking for more to put on your HR Dashboard you may want to check out this Gantt Chart / Traffic Light report.