Automated Timesheet Exporting and Sorting with Excel Macros or Power Automate - Microsoft Teams (Shifts) and Power Automate
Budget: $15 – $25 AUD
I am looking for a way to automatically export and sort timesheet data from Microsoft Teams/Shifts.
The timesheet needs to be exported weekly based on our pay period (Friday - Thursday). A new Excel document should be created for each supervisor or team leader, and each staff member should have their own sheet within their teams relevant Excel document. All data in the supervisor documents should be easily readable.
If automatic exporting is not possible, the minimum requirement is for someone to manually export the timesheet and copy it into a location where it can be automatically processed, with no additional input required from the staff member exporting the timesheets.
The data that needs to be extracted from the timesheet export includes:
Date
Clock-in
Clock-out
Break Start
Break End
Break Notes
Notes
Clock-in and Clock-out on Location data
The supervisor timesheets should calculate the following:
The total time between clock-in and clock-out to determine hours worked.
The total break time based on Break Start and Break End.
The exact hours worked, calculated by subtracting the break time from the hours worked.
These calculated hours should then be compared against a control sheet for each staff member. The control sheet contains:
Their normal clock-in and clock-out times
Break allocations (e.g., unpaid hour, paid 20 minutes, etc.)
The system should highlight any discrepancies, such as:
Long breaks
Extra hours worked
Hours worked less than the set schedule
Additionally, we have a second overall format that we want the data to be copied into. However, I am still waiting to receive a copy of that Excel document, so I do not know the exact format yet.
The third type of excel that needs to be created is an import template for Reckon Payroll software. This should be generated automatically from the first timesheet export and include all staff in one document.
All documents should be saved using a naming convention like the following along with something to differentiate the three document types:
*Team Name* - Timesheet *Date pays are being processed*
For example:
Mackay Sales Team – Timesheet 14-01-25
This is the first time I have looked to have work like this completed so I have tried to include as much information as possible, please feel free to contact me and ask any questions you may have.
I can provide a copy of the raw timesheet export from Teams, a timesheet that has been manually processed, the import templates and current master timesheet we use to review hours worked and then transpose into our payroll software.
The timesheet needs to be exported weekly based on our pay period (Friday - Thursday). A new Excel document should be created for each supervisor or team leader, and each staff member should have their own sheet within their teams relevant Excel document. All data in the supervisor documents should be easily readable.
If automatic exporting is not possible, the minimum requirement is for someone to manually export the timesheet and copy it into a location where it can be automatically processed, with no additional input required from the staff member exporting the timesheets.
The data that needs to be extracted from the timesheet export includes:
Date
Clock-in
Clock-out
Break Start
Break End
Break Notes
Notes
Clock-in and Clock-out on Location data
The supervisor timesheets should calculate the following:
The total time between clock-in and clock-out to determine hours worked.
The total break time based on Break Start and Break End.
The exact hours worked, calculated by subtracting the break time from the hours worked.
These calculated hours should then be compared against a control sheet for each staff member. The control sheet contains:
Their normal clock-in and clock-out times
Break allocations (e.g., unpaid hour, paid 20 minutes, etc.)
The system should highlight any discrepancies, such as:
Long breaks
Extra hours worked
Hours worked less than the set schedule
Additionally, we have a second overall format that we want the data to be copied into. However, I am still waiting to receive a copy of that Excel document, so I do not know the exact format yet.
The third type of excel that needs to be created is an import template for Reckon Payroll software. This should be generated automatically from the first timesheet export and include all staff in one document.
All documents should be saved using a naming convention like the following along with something to differentiate the three document types:
*Team Name* - Timesheet *Date pays are being processed*
For example:
Mackay Sales Team – Timesheet 14-01-25
This is the first time I have looked to have work like this completed so I have tried to include as much information as possible, please feel free to contact me and ask any questions you may have.
I can provide a copy of the raw timesheet export from Teams, a timesheet that has been manually processed, the import templates and current master timesheet we use to review hours worked and then transpose into our payroll software.