help creating dynamic work schedule to auto search and populate worker's hours

[Absolute123]

Hi folks,

I'm having difficulty finding a better solution for a work schedule that will sum the total hours of a worker based on the date. I've currently use sumifs but this require an extra dates column in front of each set of timein/timeout/name. My current formula looks something like this:
=(sumifs([end time],[name column],[name],[dates column],[date])-sumifs([start time],[name column],[name],[dates column],[date]))*24

The problem is I want others to use this sheet to input the hours/names and I'm afraid when they clear the input ranges (say for a new month/week), they will also clear the dates column (which is hidden). I've included a link to a sample. Any advice would be most appreciated.

Objective:
Mgr - to input schedule and see total hours for each staff per week and day-to-day total hours
<style type="text/css"><!--br {mso-data-placement:same-cell;}--></style>
https://docs.google.com/spreadsheets/d/1w_S9l330YwMYyGGiRRFG1twtAkG855BTlVPJw4S6kHw/edit?usp=sharing

 

[HackSlash]

There are many ways to approach this.

1. Hide the column
2. Unlock the columns that you allow edit and "protect sheet"
3. Put your math on a separate sheet, hide that whole sheet
4. Make a macro that adds the formula back if it's lost

 

[Absolute123]

There are many ways to approach this.

1. Hide the column
2. Unlock the columns that you allow edit and "protect sheet"
3. Put your math on a separate sheet, hide that whole sheet
4. Make a macro that adds the formula back if it's lost

Yes, I did hide and protect the columns. I forgot to mention the sheet is uploaded to google so it's easily accessed by all managers. When the sheet is copied (to create a new month for example) all the protection is lost.

I understand this is a downside of using google sheets, however for ease of access to everyone, I'm hoping there is another solution. Or perhaps any suggestions for a different layout that would provide the above objectives?

 

[HackSlash]

If you insist on using Google sheets then you might want to move the question over there because you will be working with their limitations and not ours.

I would recommend Office365 to get the shared features you want:
https://support.office.com/en-us/ar...fice-365-7032D12F-528C-49E1-A366-E48B93307D45