Creating a timesheet with Sharepoint Lists

Question

I have a system of spreadsheets that are used to collect volumes of items processed, times that people work and, based on task times that sit on a reference tab, productivity for each staff member is calculated. Each team member has their own data entry sheet that they fill out every day. This sheet has several cells per task so they can make numerous entries per task that will add up the volume of items processed. They also do diverted work that is entered as minutes spent on each diverted task (also with numerous cells to add up to a total for each task). The times in/out/time for breaks and lunch are also recorded on here. Once completed for the day, they then add their details to a datasheet that collates all of this information so MI for each task can be extracted (volumes).

The sheet is full of macros that are no longer supported by anyone and I am the only one that can fix it so I am looking for a way to replace it with the Microsoft suite of tools. I feel there is a solution somewhere with Forms as a front end for the team members to fill out and then have it sent to a back end in a List but I keep running into little issues everywhere and I am unsure if I am going to make something more complex than I already have by doing this.

Two of the main issues are:

1. I am unsure how to make a timesheet using lists as you can't seem to enter times and then I am unsure how I would be able to calculate hours worked
2. I need to be able to multiply the amount of items processed by the time allocated to that task to work out a total core time with which to calculate the productivity. Currently I use a separate tab with all of the task times on it that way if the times change then I can easily change them. The inability to use complex (and they aren't really that complex really) formulas in lists seems like it may be prohibitive.

Is there a combination of these tools and maybe Power Automate/BI/Apps that might work with this? I want this as easy as possible for the end user as they are not that tech savvy.

Thanks for any advice you might be able to send my way!

Answer 1

Hi BJ42,

Thanks for sharing updates with us and I really appreciate your effort and your precious time doing those tests. 

As you mentioned, "The problem is when it is transferred to Sharepoint the time is converted to integers. (original data entry table below prior to moving to Sharepoint)." when we trying to import an Excel data Table (the table includes a time Column.) to SharePoint List. The Flow can't import such Excel time column into SharePoint List directly. You need to format time in Flow like in below screenshot. Please refer to below Flow.

Flow:

![Image](https://learn-attachment.microsoft.com/api/attachments/d0c8263c-f477-4aeb-9add-0c6b2a0f024e?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/0623e357-892f-49b9-b700-f3cc04d2e6e5?platform=QnA" rel="ugc nofollow">

Result:

I appreciate your understanding and stay safe!!

Best Regards

Waqas Muhammad

Answer 2

Hi BJ42,

As per your description, I search a lot and unfortunately, there is no out of box feature to achieve the requirement in SharePoint online List.

As you mentioned, “I am unsure how to make a timesheet using lists as you can't seem to enter times and then I am unsure how I would be able to calculate hours worked”I assume if you enter data information in one SharePoint online list 1 for example list1 with two columns (task and task completed hours column). In another SharePoint list you use LOOKUP column ((information already on this site) to populate task and hours. See image below for data inform list.

LOOKUP column in another list.

Regarding to “ I need to be able to multiply the amount of items processed by the time allocated to that task to work out a total core time with which to calculate the productivity.” as I’m not sure but I assume does it mean if multiple user complete same task and you want to calculate total hours? e.g. three users complete same task(TastB) and calculate the total hours of TastB. If I’m misunderstood your scenario, please feel free to correct me and I will try by best to find a way for your requirement.

I appreciate your understanding and stay safe!!

Best Regards

Waqas Muhammad

Answer 3

Hiya - thanks for your response - I have taken it in and had a think about how to go about some of this and I have changed tack slightly now. I will be looking at keeping my original Excel Data Entry sheet and then use Power Automate to move it to the Sharepoint List.

I still need to figure out how to work the timesheet element and after reading a slew of threads I still don't see a clear way to do this. The timesheet is below - the team members would fill out their times in 24 hour clock in and out. The total time worked per day is calculated from those times and the flex is calculated based on the time worked in minutes and shows the amount of time under or over the contracted hours. The problem is when it is transferred to Sharepoint the time is converted to integers. (original data entry table below prior to moving to Sharepoint).

AM In	08:00:00
Break Out	10:00:00
Break In	10:30:00
Lunch Out	12:00:00
Lunch In	12:30:00
Break Out
Break In
PM Out	16:15:00
Total time worked per day (hh:mm)	07:15:00
Total time worked per day (minutes)	435
Flex (minutes)	15
Contracted Hours (minutes)	420

Answer 4

You could use a calculated field to convert the date to a date format, but I wonder if you've considered using a purpose built timesheet tracker app rather than Sharepoint/excel? I think there are some free ones out there that will certainly be easier to manage than Sharepoint.

Answer 5

Hi BJ42,

Feel free to post back if you need further assistance.

Best Regards,

Waqas Muhammad