+ Reply to Thread
Results 1 to 4 of 4

More effiecnet formulas for my time card form - suggestions please!

  1. #1
    Registered User
    Join Date
    08-31-2016
    Location
    South Dakota
    MS-Off Ver
    2016
    Posts
    10

    More effiecnet formulas for my time card form - suggestions please!

    Hello all,

    The company I work for has been going through this transition and they are asking all employees to fill out time cards, and manually track their punches. Prior to this, office employees did not track their time and we had time clocks to capture punches. I came up with this spreadsheet for myself and they've adapted it for all office employees to use for the time being.

    I was wondering if there was a better way to capture this information, preferably with formulas as a lot of the people using this are not very good with excel/computers in general. I've attached a copy of my form. I did blank out some information that could be considered confidential in the Category tab.

    So how this works is, I record my punches on the left, in the grey boxes. To the right of each days grey box, there are columns to enter the amount of time you spent and what category/code that time falls under. The description box is for personal use and does not have to be filled out.

    For codes I have the general work codes for vacation, sick, break, holiday, etc. The categories are a bit different. There are 6 different categories you can pick (A-I below each category are just examples of tasks that fall into that category and have no bearing on the form).

    The blue boxes on the right will automatically pull numbers for each of the codes I have listed and total them.

    My supervisor has informed me that we will also be using project codes... for example, I entered project "302" under a few days in this example form. Does anyone know if there is an easier way to automatically capture this information and pull it into the blue boxes? I could add "302" to the list but then when project 303 comes along I will also need to add that... I thought of just adding a spot to manually enter times but that seemed to be really confusing and disliked by the people I showed it to.

    Does anyone have an suggestions? My supervisor told me she would like me to keep the design the same, so not to confuse people. She also prints these off each week for a record so I can't really keep adding to my list of codes.. or they won't fit on one page.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: More effiecnet formulas for my time card form - suggestions please!

    Oh yikes. I would create a standardized dataset in ONE table where you have a column for the following:
    Full Name
    Date
    Maybe Category (1-6) if that is needed
    Time In
    Time Out
    Time In After Lunch
    Time Out After Lunch
    [Calculated] Hours Worked [that day]


    Essentially creating a table like that allows for VERY easy reporting and consumption (filters and such).
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Registered User
    Join Date
    08-31-2016
    Location
    South Dakota
    MS-Off Ver
    2016
    Posts
    10

    Re: More effiecnet formulas for my time card form - suggestions please!

    Thank you for the reply!

    This sheet is a electronic mimic of the paper time sheets we fill out in production. They are required to keep track of the jobs, sequences and time spent on all product they work on. That is why it is set up like this. They talked about having us fill out paper time cards like production, but having it electronically reduces the need for manual totaling. The person who prepares payroll and bills gathers information from both electronic and paper time cards and enters them into another spreadsheet. It obviously takes her a lot longer to enter the paper time cards because she is required to check everyone's math. On the electronic time cards she can just copy/paste values from this form to her spreadsheet.

    The issue I have with one table is, frequently there are multiple in/out punches. People leaving for an hour for a doctors appointment and coming back. If you leave the premises during break you are also required to punch out and back in. We are required to keep records of every single punch.

    The 1-6 codes are required because we've recently transitioned into contractual manufacturing company. We lost a lot of our previous methods of recording this information. The 1-6 codes totals are given to our financing department, whom then bills companies associated with 1-6 for the time spent on their product.

    The introduction of Project Codes is how my supervisor wants to keep track of all the time needed for the documentation and implementation for new product in our system - this time will also be billable to the company we are providing services to. These codes will not be a constant like 1-6, they may only be relevant for a week or two and then never used again.

    So in short... this information is eventually entered into one standardized table. Its gathering that data from 50 different people and keeping detailed records of their punches and how they spend their time each day; then making that process as automatic as possible is the goal.

  4. #4
    Registered User
    Join Date
    09-24-2015
    Location
    Delhi, India
    MS-Off Ver
    MS Office 2013
    Posts
    65

    Re: More effiecnet formulas for my time card form - suggestions please!

    hi Abublitz,


    =SUMIFS($C$5:$C$19, $D$5:$D$19,B39)+SUMIFS($H$5:$H$19, $I$5:$I$19,B39)+SUMIFS($M$5:$M$19, $N$5:$N$19,B39)+SUMIFS($R$5:$R$19, $S$5:$S$19,B39)+SUMIFS($W$5:$W$19, $X$5:$X$19,B39)+SUMIFS($W$24:$W$38, $X$24:$X$38,B39)


    copy paste this in Cell "C39".

    Thanks
    Rohit

+ 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. [SOLVED] Input data form needs better rules and need some idea's and suggestions please
    By Missus Mommabear in forum Excel Programming / VBA / Macros
    Replies: 52
    Last Post: 09-10-2015, 12:45 PM
  2. Budgeting out percentages of time from a time card calculation sheet
    By mhadaway in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-24-2015, 05:05 PM
  3. need help to make stock card with data entry form
    By malikshakeel in forum Excel General
    Replies: 0
    Last Post: 04-09-2013, 03:56 PM
  4. Suggestions needed on filling in a form automatically
    By carden2 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-29-2013, 03:09 PM
  5. Replies: 7
    Last Post: 05-18-2009, 02:00 PM
  6. Problem using time and formulas with a form
    By D3Pratt in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 03-28-2009, 02:21 AM
  7. Creating a form / card
    By dhunter84 in forum Excel General
    Replies: 3
    Last Post: 04-11-2006, 03:15 PM

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