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.