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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

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,130,042
Messages
5,639,731
Members
417,108
Latest member
Thein Than

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top