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

Absolute123

New Member
Joined
Dec 29, 2016
Messages
2
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

HackSlash

Active Member
Joined
Nov 18, 2016
Messages
360
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

New Member
Joined
Dec 29, 2016
Messages
2
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,521
Messages
5,469,136
Members
406,638
Latest member
Jack_Johnson

This Week's Hot Topics

Top