Timesheet automation

Ian Waites

Hi All,

I have recently started learning smartsheet, and I am looking for a way to solve this problem:

I have a sheet with multiple employees on both payroll and subcontract status. I have inputted their hourly rate in multiple scenarios (e.g. single time, overtime weekday, overtime saturday, overtime sunday, on site working, off site working, travelling time, rate when overseas etc.).

I have a timesheet sheet in excel which mirrors a printed grid sheet. Employees fill out the grid with their project reference number for each day in half hourly increments. They also note whether they were on site, off site, travelling, overseas etc. We transcribe the printed info on to the excel sheet and it calculates hours worked per week and per project. I would like the timesheet that I currently have in excel to be instead filled out in smartsheet, and then create a workflow to populate a third sheet which would contain the logic to calculate the employees weekly wage and the cost to each project, looking at the hourly rate sheet to apply the correct rate based on wether they were on site, were eligible for overtime, travelling etc. I feel that with a little time and research that I could get most of the way there, but the difficulty comes in the complexity of multiple scenarios applying to the hours, and how to easily transcribe this on to the timesheet.

It feel it would be viable if the smartsheet functions could read text format and fill colour. That way we could fill cells a certain colour if they are one scenario and italic if they are another, and recognise both scenarios as applying to a specific block of time.

Apologies if this makes no sense. I would be hugely grateful for any advice.

Ian

Mark Cronk

Hi @Ian Waites ,

Moving your process into Smartsheets is very doable but setting it up will take time.

You can use conditional formating to determine the look of a cell, or range of cells in a row, based on the value of a cell in that row. However, you can't apply formula logic or automation based on format.

Your solution will be sets of reference sheets with employees, rates, projects, and dates/hours. Then using VLOOKUP or INDEX-MATCH and SUMIFS formulas to calculate values on sheets that are used to generate reports. The reports could be made to look like your current excel solution.

Employees would enter time with forms, reports or sheets.

Have you looked through the templates? There might be a solution, or partial solution, available to you there.

There are lots of very talented smartsheet users in this Community to help you too.

Good luck,

Mark

Ian Waites

Thank you for the fast reply, Mark. I will need to get my thinking cap on.