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.

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