• 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.

Swing shift time schedules

Lawrence Dodge

Lawrence Dodge

Member
Hi all--

We are on a 24/7 schedule where I work with 4 teams A-1st, A-2nd, B-1st, B-2nd. We have a spreadsheet with a timestamp (mm/dd/yy hh:mm:ss). Currently I use this:

=IF(MOD($A6,1)<VALUE("5:00"),2,IF(MOD($A6,1)<VALUE("17:00"),1,IF(MOD($A6,1)<VALUE("24:00"),2,"")))

which figures out first or second shift. What I need to do is figure out A team or B team. A team works 2 days, off 2 days, on 3 days, off 2, on 2, off 3. B team's schedule is the mirror of this.

I converted the date to a number and starting on 24 Oct '11, I got the number "40840", I thought to put in an if cell A6 (where the time stamp is) = 40840 + 14k with "k" being an interger value then "A", if false then "B" -- if I do this for the seven days team A works from 10-24 to 11-6 it should work. My delemna is I do not know how to format the mathematical equation "=40840 + 2k"-- how do I enter an integer multiple into an "if" statement
 
so A1,A2,B1,B2 all working on different shifts? wouldn't it be easier to have the first shift be like


If A1 starts on first shift, A2 starts from A1's time + 5 hours? In excel, hours are decimal number 1 divided by 24 or 0.04166666666666666? i.e. so you can have A1's time + 1/24*5?


Also, you have not specify the relationship between team 1 and team 2. if you can elaborate on the end picture may be I can help you out with this. What's the K for? I don't understand what you was saying.
 
Not sure I got all of that...

Maybe if you took a stab at writing the formula (even a pseudo formula) that would help?


For your first formula, if you want to cut down on length and/or save some nesting levels, you can reduce it to:

=LOOKUP(MOD($A6,1),{0,5/24,17/24},{2,1,2})
 
Thankyou Luke for the shortened formula:)

As for the shift work, Team A has a morning crew that comes in at 5 am and and evening crew that comes in at 5 pm on days they work. Team B the same thing. This is figured out with the Lookup(mod......) formula

What I would like to do is figure out which team worked on a given day. Since they only work one Monday in a two week period (starting at Oct 24 or 40840 in Excel numbers)I figured I could take 40840 and add multiples of 14 to figure out which Mondays team A works. I thought it should be written something like this:

=if(A6 = 40840 + 14 * an integer,"A", "B")the thing I don't know how to do is add multiples of 14 to a number.

For instace, I could write X = 2y +1 which would give an odd number. If you have a better way to figure out if it is the first Monday or second Monday (and all the other days) of a two week period, I would be greatly appreciative :)
 
So, if A6 is some multiple of 14 after 40840...

=IF(MOD(A6-40840,14)=0,"A","B")

This should give you "A" for every 2nd Monday after October 24, 2011.
 
Why can't it be simply a date+14? It is very hard to visualize. I thought you want to view the team by team breakdown?


https://docs.google.com/spreadsheet/pub?hl=en_US&hl=en_US&key=0Avy2Dz3bgeEHdHByRWRDSjBVUGdkOFQwb3Y1Y25vR0E&output=html
 
I have the date, I am trying to figure out which team worked on a certain date. But yes it could be date + 14. So how do I tell Excel to look at a given date and see if it is Oct 24 + a multiple of 14?
 
ah, i see. Use Luke's method. if the mod() comes up 0,1,4,5,8,9,10 then it's date they have to work.


=IF(OR(MOD($H$22-$B$2,14)=0,MOD($H$22-$B$2,14)=1,MOD($H$22-$B$2,14)=4,MOD($H$22-$B$2,14)=5,MOD($H$22-$B$2,14)=8,MOD($H$22-$B$2,14)=9,MOD($H$22-$B$2,14)=10),"work","rest")


where B2 = 10/24/11

H22 is a date in the future.
 
Fred and Luke that's a really impressive approach, the penny just dropped for me on why you are using MOD, well done!
 
Hi ,


The requirement specified by Lawrence is :


A team works 2 days, off 2 days, on 3 days, off 2, on 2, off 3.


This should result in the following output :


Work , Work , Rest , Rest , Work , Work , Work , Rest , Rest , Work , Work , Rest , Rest , Rest.


The checks should then be for 0,1,4,5,6,9,10 ; is this correct ?


Narayan
 
Narayan yes you are right that is the right sequence '0,1,4,5,6,9,10, I figured Fred was just out a bit for some reason but the approach was spot on as I had to put it into a worksheet to figure out how it worked.
 
Hi all--

Thank you for your help. It works almost. I figured out the typo of 6 and 8. But if you could please look at my worksheet there is still a bug I can't find.

When I typed it exactly like Fred (thanks a ton) did with the typo corrected, I got column c but all the most entries were backward, when I switched a and b in the mod equation, everything switched and now only four entries are wrong. Another thing I don't understand is that cell B5 with date / time is wrong yet cell B7 with date / time is correct. I changed the mod equation 4 to 11 and it worked except with the time stamps

Any thoughts?

(I was going to attach the actual worksheet, but I guess I can't on replies)

[pre]
Code: 
40840						

CALC'ed		4 to 11	ACTUAL	$B3-$B$1	$E3-14
24-Oct	a	b	b	A	0
40840.47814	b	a	a	A	0
40841	a	b	b	B	1
40841.50878	b	a	a	B	2
40842	b	a	a	A	2
40843	b	a	a	A	3
40844	a	b	a	A	4
40845	a	b	b	B	5
40846	a	b	b	B	6
40847	b	a	a	A	7
40848	b	a	a	A	8
40849	a	b	b	B	9
40850	a	b	b	B	10
40851	b	a	b	B	11
40852	b	a	a	A	12
40853	b	a	a	A	13
40854	a	b	b	B	14
40855	a	b	b	B	15	1
40856	b	a	a	A	16	2
40857	b	a	a	A	17	3
40858	a	b	a	A	18	4
40859	a	b	b	B	19	5
40860	a	b	b	B	20	6
40861	b	a	a	A	21	7
40862	b	a	a	A	22	8
40863	a	b	b	B	23	9
40864	a	b	b	B	24	10
40865	b	a	b	B	25	11
40866	b	a	a	A	26	12
40867	b	a	a	A	27	13
40868	a	b	b	B	28	0
[/pre]
 
the time stamp which is the format of the inputed data, is really unreliable, some times it works, some times no. Any thoughts?

LCD
 
The time stamp is throwing things off because those extra decimals are causing the MOD function to not evaluate out to perfect integers. This causes the formula to tend towards the FALSE evaluation more often than it should.

I've lost track of what your exact desired output needs to be, but here's a revised formula that states who's working on a given date:

=IF(OR(MOD(INT(B2)-$B$1,14)={0,1,4,5,6,9,10}),"A","B")


I'm still working on understanding the time problem...
 
For time, I think it works better to have a 2nd helper column. Assuming the first formula I gave is in col C, you can use this column to correct for specific time:

=IF(MOD(B2,1)<5/24,C1&"2",IF(MOD(B2,1)>17/24,C2&"2",C2&"1"))


This states that if the time stamp is before 5:00 am, it should be whichever crew was assigned to last night (2nd shift). If the time is after 5:00 pm, assign to 2nd shift of crew for that day, otherwise it's 1st shift.


If some of the dates don't have time stamps, we can add an extra logic check statement to workaround, like this:

=IF(AND(INT(B2)<>B2,MOD(B2,1)<5/24),C1&"2",IF(MOD(B2,1)>17/24,C2&"2",C2&"1"))
 
Luke you are SOOO awesome. Where and how long did it take you to learn code. I really would like to learn it but don't know how to start

Thanks again

LCD
 
You're very welcome, Lawrence. I was first introduced to Excel forums about 6 years ago, and have since just been doing as much reading on forums and blogs (like Chandoo's) that I can. Plus, I have to admit, I'm one of those "geeks" who likes tackling math/logic problems just for fun. =)
 
You must log in or register to reply here.
Back
Top