+ Reply to Thread
Results 1 to 5 of 5

Staff clock data conversion into time table per staff member

  1. #1
    Registered User
    Join Date
    05-28-2013
    Location
    Cradock
    MS-Off Ver
    Excel 2010
    Posts
    4

    Staff clock data conversion into time table per staff member

    Hi,

    A friend recently purchased a new fingerprint clock for his staff to record their working time. The staff start early, have a break round about 09h00 for approximately 30 min and then work until lunch. Take 1h30 lunch break, and then work until the end of the day.

    The data downloaded from the clock is in the sheet "Raw Data". The format I would like to convert the data to is shown in sheet AttendRecord - the data in the "RawData" sheet was not used to compile the table in AttendRecord, it is just to indicate the required format of the converted data.

    Can someone please assist. It can be done manually, but it will take a considerable amount of time.

    Please assist with a formula to convert the raw data into the table format data for each staff member.

    Kind regards,
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,124

    Re: Staff clock data conversion into time table per staff member

    Here is my shot at it. The partial result is on the New Record sheet. If you copy and paste A5:AC12 as a block and change the person’s name, the formulas should fall into place.

    I converted the Attend Record to an Excel table mainly because formulas that reference tables do not have to be changed when the number of rows in the table changes. You can select all rows in the table, right click and select delete -> table rows, and then copy and paste the new data in and everything will work fine.

    I added a couple of helper columns to the table - another advantage of tables is that they "remember" formulas and copy them down.

    The first helper column is Day =TEXT([@DateTime],"mm/dd") – this is a combination of month and day – it should still work even if your regional settings are different, but if you do change this, you’ll have to change it in the SUMIFS formula later on.

    The next helper column is time =[@DateTime]-INT([@DateTime]) – this is the time portion of the date time with the date part stripped out.

    The next helper column in Punch Num =SUMPRODUCT(([Name] = [@Name])*([Day]=[@Day])*([DateTime]<=[@DateTime])) – this is the punch number for that person for that day.

    There is a Lookups Sheet. This sheet contains a small pivot table that contains a unique list of all people in the data. This is overlaid with a named dynamic range so you can look up a person using a dropdown list. On the New Record sheet I look up the ID from Table_Data. You will probably need another table to match person with department.

    Also on the lookup sheet is the start date. This is the minimum date in the data.

    The Start_Date is seeded in Cell A6 on the New Record sheet, and the next 29 days are calculated from it (Columns B:AC). These are also shown in m/d format. You can change them to suit your regional settings and it will not affect any of the calculations.

    Each cell has a formula: =SUMIFS(Table_Data[Time],Table_Data[Name],$J5,Table_Data[Day],TEXT(A6,"mm/dd"),Table_Data[Punch Num],1)

    Where $J5 is the person’s name,
    A6 is the date – remember to convert it to regional settings if you change it in the raw data.
    And the punch number varies from 1 to 6 in order down the rows.

    I can get away with SUMIFS since under these conditions, there is a unique record.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    05-28-2013
    Location
    Cradock
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Staff clock data conversion into time table per staff member

    Thank you very much dflak,

    It works 100%. I need to educate myself with some of the functions I have not used before, but that's how we learn. Thank you very much for taking the time to help. I really appreciate it.

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,124

    Re: Staff clock data conversion into time table per staff member

    To learn about dynamic ranges see this article: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges.

    To learn more about tables, see this article: http://www.utteraccess.com/wiki/Tables_in_Excel.

    Good luck with the project.

    P.S. Although SUMPRODUCT isn't an array formula, it acts like one. This article gives some insight into how it works: http://www.utteraccess.com/wiki/Array_Formulas
    Last edited by dflak; 03-02-2017 at 04:31 PM. Reason: Add PS

  5. #5
    Registered User
    Join Date
    05-28-2013
    Location
    Cradock
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Staff clock data conversion into time table per staff member

    Thanks, will definitely study those articles.

+ 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] Searching workbook by staff member name
    By Matt Hustwaite in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-28-2016, 03:56 AM
  2. [SOLVED] Count the number of staff working between times based on staff position
    By sparky1978 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-03-2016, 08:10 AM
  3. [SOLVED] Macro to update Score Card Summary when the new staff add-in or existing staff deleted
    By Faridwahidi in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-12-2014, 02:43 PM
  4. Calculate Leave Accural In a year using staff member tennur
    By SalientAnimal in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-03-2014, 06:54 AM
  5. Providing how many times staff members have been late by staff ID
    By SG56001235 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-18-2013, 10:47 PM
  6. Staff Planner - How To Set Up UserForm to Generate Data into Staff & Date Spreadsheet
    By Marie Snell in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-19-2013, 11:04 PM
  7. Which staff member has the most sales in this month?
    By shazzm in forum Excel General
    Replies: 2
    Last Post: 04-28-2011, 03:46 AM

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