Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
rota calculations
I have a rota spreadsheet with dates across the top ( a week at atime)
and shift timings down the side ( for each day). The number of hours per shift is calculated from the shift times and stored in a hidden column Shift names are entered from a look up table to fill each available shift according to a preset pattern. If no one available for shift vacant is entered as a default. All this is colour coded not a figure in sight and makes it visually easy for everyone to know what they are working each week. My question is what formula (?vlookup and/or pivot table? ) can I use to calculate for each weekly spreadsheet the total hours worked by each person. I cannot get my head around the look up of hours is contingent on the row ( ie shift) they are working. Each person can work multiple shifts in the same day, the shift pattern is different at weekends so the spreadsheet has blanked out boxes where appropriate Thanks -- Mike |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
rota calculations
eventhorse wrote:
I have a rota spreadsheet with dates across the top ( a week at atime) and shift timings down the side ( for each day). The number of hours per shift is calculated from the shift times and stored in a hidden column Shift names are entered from a look up table to fill each available shift according to a preset pattern. If no one available for shift vacant is entered as a default. All this is colour coded not a figure in sight and makes it visually easy for everyone to know what they are working each week. My question is what formula (?vlookup and/or pivot table? ) can I use to calculate for each weekly spreadsheet the total hours worked by each person. I cannot get my head around the look up of hours is contingent on the row ( ie shift) they are working. Each person can work multiple shifts in the same day, the shift pattern is different at weekends so the spreadsheet has blanked out boxes where appropriate Thanks Generally speaking, conditional sums can be done with SUMIF (with one criterion) or SUMPRODUCT (one or more criteria), but how these might fit your case I cannot say as I cannot visualize your actual layout. Here is an excellent resource for SUMPRODUCT in this context: http://xldynamic.com/source/xld.SUMPRODUCT.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rota compile | Excel Discussion (Misc queries) | |||
Rota Projection | Excel Discussion (Misc queries) | |||
Work Rota | Excel Worksheet Functions | |||
Rota | Excel Discussion (Misc queries) |