I need help finishing a data conversion/calculation tool in MS Excel. I first started working on it myself, but ran out of time and am in need of professional assistance.
What do I want to achieve?
Transform persons workhours data (which is extracted from timekeeping system) into easily auditable & correctable format (daily_hours), highlight erroneous rows (where total break timeis bigger than work hours), allow user to add or subtract work hours, add comments and then provide an overview (total_hours), showing total registred work hours, total break hours and calculate the total payable time (total hours - break time), and transpose comments from daily_hours sheet.
User inserts manually a set of names and ids, presumably corresponding with the data content user has pasted into data and data2 sheets.
comprises of 4 columns:
A - date
B - person ID
C - total hours
D - total break time
The data sheet should be static - the user should be able to paste any quantity of rows of data onto the data sheet (depending on how many person's data is the user is currently viewing), without having to worry about overwriting any vital formulas.
This sheet houses door movement data. When a person moves through viewed door, the system registres date, time, person ID and the direction comprises also of 4 columns:
A - date
B - time (hhmm)
C - person ID
D - direction (1 - in, 2 - out)
This sheet should show the data from “data” field, replace person ID with the according person name from “persons” sheet, then calculate “start of shift” and “end of shift” using the door movement data from “data2”, add the possibility to subtract or add hours, comments. It should also show weekdays and week numbers on each row.
As the sheet name indicates, this is the total's page - it should show a list of persons (based information entered on the “persons” sheet), then sum the work hours and break time of said person, calculate total payable time and transpose whatever comments have been written down regarding the current person on the comments column in “daily_hours” sheet.