+ Reply to Thread
Results 1 to 3 of 3

Timesheet - Counting negaitve time (early punches)

  1. #1
    Registered User
    Join Date
    01-18-2013
    Location
    Funtown, USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Timesheet - Counting negaitve time (early punches)

    Hello,

    I have created a timesheet to help my management team better track arrival times and the amount of time spent on lunch breaks. This info can (somewhat) be pulled from the electronic timeclock system we all use, however, none of the reports pull out in a format that can show the results I'm looking for, and usually require a manual count of schedule deviations. Corporate likes our employees to be fairly exact to their schedule and they look for punches that are 3 minutes, or more early, and 3 minutes, or more, late.

    I've got the scheduled and actual arrival times, as well as the scheduled and actual lunch durations calculated, and need the punches, the scheduled times and the calculations to display in hrs/mins (done). I'm almost ready to release this thing, but in the top right corner of the sheet, I have totals to give a quick count of late or early arrivals.
    The late arrivals (more than 3 minutes late) were easy to count. I’m having difficulty in counting the arrivals that were more than 3 minutes early. It seems that the negative time is much more difficult for excel to work through, but I could be wrong on that.

    I’d like some help figuring out how to count the arrivals that were 3 minutes, or more, early and the lunches that ended 3 minutes, or more, early. I want this count to appear in Q4 (arrival compliance) and T4 (lunch compliance). Thank you.

    2014 Schedules and Discrepancies - Example.xlsm

  2. #2
    Forum Contributor
    Join Date
    07-24-2009
    Location
    Valrico, FL USA
    MS-Off Ver
    Excel 2016
    Posts
    358

    Re: Timesheet - Counting negaitve time (early punches)

    Try the following formula in cell Q4:

    =ROUND(COUNTIF(F:F,"<="&-"00:03:00"),3)

    A similar one for T4

  3. #3
    Registered User
    Join Date
    01-18-2013
    Location
    Funtown, USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Timesheet - Counting negaitve time (early punches)

    ncmay! That worked! Thank you!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 05-20-2023, 01:07 AM
  2. [SOLVED] IF function: Early/On Time/Late Time vs. set window of time
    By hclark579 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-21-2014, 05:37 PM
  3. Need help in auto rounding off time when clocking-in early
    By stealth_0206 in forum Excel General
    Replies: 1
    Last Post: 06-12-2012, 02:15 AM
  4. Timesheet - Early Start Hours
    By Vibro in forum Excel General
    Replies: 0
    Last Post: 10-24-2011, 09:05 PM
  5. Prompt early log out time using msgbox
    By stoey in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-21-2009, 07:42 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1