Hey, I have just taken over the responsibility of completing the rotas for staff at work and I'm trying to work from the old system as much a possible.
I want it to calculate the number of hours worked per week for each individual and put it into the subtotal section without changing the format massively is this possible.
Here is a link to what im working with http://tinypic.com/r/ae2zw3/5
Sorry for double post just realised i could attach an example workbook
If it's as simple as establishing working days then perhaps:
=8*SUM(COUNTIF(C3:C9,{"COURSE","*-*"}))
If the shift length varies from 8 hours (not so in your file) then things become a little more complex.
Let us know.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Whilst I'm around I figure I'd post a solution to cater for possibility of non 8 hour shifts, eg:
=SUM(IF(ISNUMBER(--LEFT(C$3:C$9)),MOD(REPLACE(C$3:C$9,1,FIND("-",C$3:C$9),"")-LEFT(C$3:C$9,FIND("-",C$3:C$9)-1),12)))+8*COUNTIF(C$3:C$9,"Course")
confirmed with CTRL + SHIFT + ENTER
Note the point about the above requiring Array entry... note that for COURSE an 8 hour day is assumed.
That has worked perfect thankyou so much!
One more thing would it be possibe to have it show up red if i have booked in a late to an early like shown in the example
Using your sample file...
If you highlight range C12:J12 (selecting C12 first), set the default fill to Green, then apply the following Conditional Formatting Formula rule of:
set format for this condition to be Red
Please Login or Register to view this content.
You my friend are amazing thankyou so much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks