• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Shift calendar [SOLVED]

T

The Planner

New Member
I have created a shift calendar that calculates in this instance a 14 on 7 off shift rotation with 3 shift groupings. conditional formatting displays the shifts on a perpetual gantt style showing the shifts against the list of employee names.

Conditional formatting works well as long as the first date in the rotation of shift 1 is in the past.

This formula looks through the shift pattern dates using vlookup and named ranges based on the gantt dates and sticks in a corresponding shift number to trigger the formatting.

[pre]
Code: 
=IF(AND($H6=1,VLOOKUP(I$3,Ashift,1,TRUE)<=I$3,VLOOKUP(I$3,Ashift,2,TRUE)>=I$3),1,IF(AND($H6=2,VLOOKUP(I$3,Bshift,1,TRUE)<=I$3,VLOOKUP(I$3,Bshift,2,TRUE)>=I$3),2,IF(AND($H6=3,VLOOKUP(I$3,Cshift,1,TRUE)<=I$3,VLOOKUP(I$3,Cshift,2,TRUE)>=I$3),3,"")))
[/pre]

I would like to make this a little more dynamic so that I can alter the shift pattern and its calculations but have the gantt remain stable.


a copy of the file can be found here


https://docs.google.com/file/d/0B93dszGYZ3tjNXJnc0RITzNtd0U/edit?usp=sharing
 
Hi, The Planner!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


You should grant public access to the uploaded file.


Regards!
 
Hi, The Planner!


Would you explain what do you mean by changing the shift pattern?


Despite of this, if you plan to split always in 3 your A:C table range, you could change your AShift, BShift and CShift named ranges to these dynamic named ranges definiton:

=DESREF(tblShifts34;FILAS(tblShifts34)/3*XXX;;FILAS(tblShifts34)/3;3) -----> in english: =OFFSET(tblShifts34,ROWS(tblShifts34)/3*0;;ROWS(tblShifts34)/3;3)

Change "XXX" (unquoted) to 0 for AShift, to 1 for BShift and to 3 or CShift.


Regards!
 
By changing the shift pattern, I mean if I needed another sheet with a different pattern, say 10 days on and 4 off having only 2 different shifts, I would like the gantt formula to manage that without having to rebuild it every time.

As for the dynamic ranges for would I change it to something like this for just an ashift and bshift?


=OFFSET(tblShifts34,ROWS(tblShifts34)/2*0;;ROWS(tblShifts34)/2;3)
 
Hi, The Planner!


For 2 shifts:

1st: =OFFSET(tblShifts34,ROWS(tblShifts34)/2*0;;ROWS(tblShifts34)/2;3)

2nd: =OFFSET(tblShifts34,ROWS(tblShifts34)/2*1;;ROWS(tblShifts34)/2;3)


In general, for <I> shifts, the <J>th shift should be:

=OFFSET(tblShifts34,ROWS(tblShifts34)/<I>*<J-1>;;ROWS(tblShifts34)/<I>;3)


Always with I exact divisor of tblshifts34, i.e., shifts of same no. of rows.


About shift pattern change I'm still wondering how to.


Regards!


EDITED


PS: Sorry, I omitted posting the last formula, that of the Gantt alike chart:

=SI(SUMAPRODUCTO((tblShifts34[From Date]<=I$3)*(tblShifts34[To Date]>=I$3)*(tblShifts34[shift]=$H6)*(FILA(tblShifts34[shift])))=0;"";INDICE(tblShifts34;SUMAPRODUCTO((tblShifts34[From Date]<=I$3)*(tblShifts34[To Date]>=I$3)*(tblShifts34[shift]=$H6)*(FILA(tblShifts34[shift])));3)) -----> in english: =IF(SUMPRODUCT((tblShifts34[From Date]<=I$3)*(tblShifts34[To Date]>=I$3)*(tblShifts34[shift]=$H6)*(ROW(tblShifts34[shift])))=0,"",INDEX(tblShifts34,SUMPRODUCT((tblShifts34[From Date]<=I$3)*(tblShifts34[To Date]>=I$3)*(tblShifts34[shift]=$H6)*(ROW(tblShifts34[shift]))),3))
 
Hello @Planner,

If I am understanding your question correctly, you could use the following formula in your GANTT area to make things more dynamic:

=IF(COUNTIFS(tblShifts34[shift],$H6, tblShifts34[From Date], "<=" & I$3,tblShifts34[To Date],">=" & I$3)>0, $H6, "")


Cheers,

Sajan.
 
Hi, Sajan , thanks for your response.

I tried out your formula

=IF(COUNTIFS(tblShifts34[shift],$H6, tblShifts34[From Date], "<=" & I$3,tblShifts34[To Date],">=" & I$3)>0, $H6, "")


but it did not seem to produce the results I was looking for . It only updated every fourth day to a # '2'. and didn't trigger the format.


My conditional formatting is stable using the original formula until I set the starting date of the first shift to something in the last week or in the future. then it produces an N/A error. not that it hurts the output but it just looks bad.


My conditional formatting relies on the cell having a 1,2,or 3 in it to display the color.
 
Hi SirJB7,


I liked the look of your formula and could follow what you were trying to do. But It didn't seem to work either. I think mine is doing what I need but need to adapt your range references into it.


Your dynamic range formula works great though. Thanks.

I am still working on another variation of the sheet, where I can set the parameters in a row of what the shift is comprised of, and then have the date calculations change based on that. I don't think I have the logic worked out or enough parameters yet but I need more coffee and some star gazing before that comes to me.
 
Hi, The Planner!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Shift%20calendar%20-%20Shift%20calc%20%28for%20The%20Planner%20at%20chandoo.org%29.xlsx


It's your same uploaded file, with your A/B/C/Shifts range definitions, and my formula in range I6:V20 (first 15x15 cells of Gantt area), and it works unless I'm missing something.


Regards!


PS: An alternative to your actual planned schedule might be replacing coffee by Carlsberg.. it never fails! :)
 
Hi @Planner,

Check out your workbook modified to include my formula above:

http://speedy.sh/NfKCr/Shift-calc.xlsx


Cheers,

Sajan.
 
Thanks for you responses, I have had a chance to look at both solutions briefly. I will delve into them a little more over the next few days. But it appears both will work. I want to try a couple of different formats with the shifting. ie 2 shifts and 4 shifts to see how it stands up to the change.

I will set the status to solved for now..

thanks a gain to both of you for your creative approaches.
 
You must log in or register to reply here.
Back
Top