Tag Archives: timesheet

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!