Our company is launching a new time and attendance system that will feed into our payroll software. The output from the time and attendance system gives an excel sheet with raw data. Each line consists of a shift that was worked along with a load of data associated with that shift including length and base payrate. That raw data is pulled into a consolidated shift sheet that pulls in info about the shift and about the staff member that completed that shift.
We are at the point now where we need the spreadsheet to aggregate the data for payroll purposes. Typically, I'd approach this with a SUMIFS function, but that doesn't quite go as far as we need. For the import into our payroll software we need to sort and then sum/aggregate the total hours worked for different shift types paid at different rates. These shift types are WEEK, WEEKEND, OVERTIME, ENHANCED OVERTIME. Some people work 2 or 3 different positions, the output needs to identify this and place these shifts into WEEK 2, WEEKEND 2, OVERTIME 2, ENHANCED OVERTIME 2, or 3. Also, if shifts are done on certain dates (bank holidays), the system needs to identify that and allocate to the BH shift type. Once certain criteria are reached, the shift spills into overtime, and then advanced overtime. It will be much easier to explain, I imagine, with the spreadsheet actually in front of you.
Not sure whether this can be achieved with simple formulas, array formulas or needs a VBA macro built out. Either way, we would like the simplest approach so we can service and tweak going forward.
The excel contains some confidential info so I won't attach directly to the job - let's chat first.
In your proposal, please detail how you would approach this project, and mention any similar experience you may have had before / what qualifies you.
Hello, I’m an Excel/VBA expert and I would like to help you with your project. I'm fully available and I can start right away. Please check my profile and contact me to discuss. Regards.
Hello, I am highly skilled in Excel and VBA and can implement your required payroll within 2 days. Please share sample file and discuss details via chat. Thank you, Usman
Hidden columns can be used to run some checks to apply the SUMIFS operations, macros will be the last choice. But having the spreadsheet will give me a better idea of what you need.