Automated Timesheet Exportation and Sorting - 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 the relevant Excel document. All data in the supervisor documents should be easily readable to ensure efficient use.
If automatic exportation is not possible, the minimum requirement is 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 only data that needs to be extracted from the timesheet includes:
Date
Clock-in
Clock-out
Break Start
Break End
Break Notes
Notes
Clock-in and Clock-out on Location data
The process should also 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 final format I need automatically created is an import template for Reckon Payroll software. This should be generated automatically from the first timesheet export and saved with the same naming convention as the other document but with "Reckon Template" appended to the name.
All documents should be saved using a naming convention like the following:
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.
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 the relevant Excel document. All data in the supervisor documents should be easily readable to ensure efficient use.
If automatic exportation is not possible, the minimum requirement is 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 only data that needs to be extracted from the timesheet includes:
Date
Clock-in
Clock-out
Break Start
Break End
Break Notes
Notes
Clock-in and Clock-out on Location data
The process should also 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 final format I need automatically created is an import template for Reckon Payroll software. This should be generated automatically from the first timesheet export and saved with the same naming convention as the other document but with "Reckon Template" appended to the name.
All documents should be saved using a naming convention like the following:
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.