+ Reply to Thread
Results 1 to 3 of 3

Calculating time

  1. #1
    Registered User
    Join Date
    12-23-2009
    Location
    Jacksonville, NC
    MS-Off Ver
    Excel 2007
    Posts
    1

    Calculating time

    I have a spreadsheet with columns with time in and out, and rows for time clock times and paid times:
    PAYROLL MONDAY TUESDAY
    IN OUT IN OUT
    CLOCK TIME 1-Aug 7:58 AM 5:15 PM 8:03 AM 5:00 PM
    TIMESHEET 8:00 AM 5:15 PM 8:00 AM 5:00 PM
    CLOCK TIME 15-Aug 7:57 AM 4:59 PM 7:59 AM 4:58 PM
    TIMESHEET 8:00 AM 5:00 PM 8:00 AM 5:00 PM

    I need to be able to calculate total minutes late for each pay period of ten days (columns) and total minutes employees left early for each pay period (also ten columns). If I can figure out how to obtain the information for one pay period, then I can solve the rest easily. I need only calculate minutes later than 8:00 am, and minutes earlier than 5:00 pm.

    Any assistance you can provide me as soon as possible is tremendously appreciated.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    09-23-2008
    Location
    UK
    Posts
    137

    Re: Calculating time

    I couldn't open your file, but just do one minus the other. make sure you format the cell in which you do the subtraction as a custom hh:mm. Also make sure both input cells have the format dd/mm/yy hh:mm.

    Also make sure you reward those who are consistently early!

  3. #3
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Calculating time

    It's not as simple as firefly2k8 suggests. Each of the IN/OUT cells will have to be tested for whether or not the values is before or after the respective clock-in or clock-out time.

    Possibly an array formula could be developed for this, but I always opt for simplicity and the use helper cells.

    My suggestion is to use two rows immediately below Payroll Date as helper cells using these formulas, below, and hide the helper rows (or put them on another sheet altogether).

    Minutes in early: =IF(C3<Clockin,Clockin-C3,0)*1440
    Minutes out late: =IF(D3>Clockout,D3-Clockout,0)*1440

    Be sure to check that the correct cells references are used above. I used clocktime values instead of timesheet values.

    Note: I created two named constants used in the above formula:
    Clockin: = 0.333333333333333 -- this is 8:00 AM
    Clockout: = 0.708333333333333 -- this is 5:00 PM

    Using the formulas in a helper row iit s then just a matter of using a sum formual to calculate the total minutes in early or out late.

    You might find this link of interest: Working With Overtime Hours In Excel
    Explore the site as there is a lot of useful info there.
    Attached Files Attached Files
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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