Schedule Making Formula's

beh162

beh162

New member
Joined
Jan 15, 2015
Messages
7
Reaction score
0
Points
0
I'm a newbie to excel but I do know some of the very basics. I'm trying to transition my schedule making to excel and get off the paper system and take advantage of excel and all it has to offer. I know I have a lot to learn but I want to get a grasp on formula's better. Here is what I want to do:

I need to make sure I have coverage for when a shift ends and another shift leaves so say I have:
Employees:
12p-5p
12p-5p
5p-10p
5p-10p

So I want to make a sheet that takes my shifts and tells me I have two people leaving at 5p and two people coming in at 5p. What kind of formula would be good for this?
 
Hi beh162, I'm not quite sure what you are looking for with your formula. Do you want it to count the number of employees finishing one shift and count the number of employees starting the next shift and tell you if they are unequal?
If so could you post a small sample sheet showing the way your schedule is set out?
As in, are the start and finish times of each shift in the same cell or separate cells, or maybe just a shift number, do you want the answer of unequal/equal to be in a different cell somewhere or maybe highlight a cell of heading in red if its not equal?
Just for fun I amended one of the schedules I've worked on before to see if it might help.
It has a section where you type all the employees that are available for work on each shift (someone might not be able to work nights, and someone else might not be free on Wednesdays etc)
Then in every cell of the schedule it gives you a drop down list of names that are available for work on that shift.
It doesn't count start and finishes etc. but its easy to see a blank spot in the schedule
 

Attachments

  • 2 shift-Work Schedule.xlsx
    29.1 KB · Views: 23
Beamer said:
Hi beh162, I'm not quite sure what you are looking for with your formula. Do you want it to count the number of employees finishing one shift and count the number of employees starting the next shift and tell you if they are unequal?
If so could you post a small sample sheet showing the way your schedule is set out?
As in, are the start and finish times of each shift in the same cell or separate cells, or maybe just a shift number, do you want the answer of unequal/equal to be in a different cell somewhere or maybe highlight a cell of heading in red if its not equal?
Just for fun I amended one of the schedules I've worked on before to see if it might help.
It has a section where you type all the employees that are available for work on each shift (someone might not be able to work nights, and someone else might not be free on Wednesdays etc)
Then in every cell of the schedule it gives you a drop down list of names that are available for work on that shift.
It doesn't count start and finishes etc. but its easy to see a blank spot in the schedule
Click to expand...

Beamer,
I worked out that problem thanks!! I do have a different sheet that I attached that I could use some help on:

In Cell B5:B36 (cash) is a formula based on what numbers are keyed in AA5:AA25(Monday). The formula that goes in C4:C36 is going to be identical to B5:B:36 with the only difference is it will pull numbers that are keyed into AB5:AB25. Can I copy that formula and redirect it to pull data in AB instead of AA.Screen Shot 2015-01-15 at 5.49.08 PM.jpg
 
Yes, if cell B5 has a formula like =$AA$5 then remove the $ signs so that the formula reads =AA5, then just copy/pull across.
The $ signs lock in the Column and Row, so only remove them from the cells that you want to reference dynamically.
So I you copy that formula over 2 rows....to cell D5, then it will read =AC5.
Just remember to leave the $ signs on any cell reference in you formula that is static, e.g. referring to an amount in cell $Z$1 that all cells need to refer to.
 
Beamer said:
Yes, if cell B5 has a formula like =$AA$5 then remove the $ signs so that the formula reads =AA5, then just copy/pull across.
The $ signs lock in the Column and Row, so only remove them from the cells that you want to reference dynamically.
So I you copy that formula over 2 rows....to cell D5, then it will read =AC5.
Just remember to leave the $ signs on any cell reference in you formula that is static, e.g. referring to an amount in cell $Z$1 that all cells need to refer to.
Click to expand...

Cell B5 didn't have the $ mainly because each cell formula in row B4 has a different range that it adds, so I can't just drag the formula's down. I'm sure my first problem is what formula I used in all of B4. What I did was I copied the formula to E4 (originally I put C4 I meant E4) and it changed the formula to pull from AD instead of AB because that was 4 columns to the right of what the original formula pulled from. I guess I can physically change every one to AB just wanted to see if I could redirect it an easier way.
 
Screen Shot 2015-01-16 at 5.40.53 AM.jpgExplanation of what the formula's look like in "B"
See how I have a "1" in cell AA15 and on the left side there is a 1 in cells B12:B31 = 1 person from 11-7:30. Basically I need the shifts in Y,Z to fill in on the time range in "B" and add based on the number of shifts. So for 5:30 the formula is sum=AA20:AA24,AA15:AA24,AA10:AA13) because we have to get rid of 12-5 because that person isn't there at 5:30 and had to get rid of 11-4 because that person isn't there so shouldn't be counted for.

Did I make this harder then it needed to be? Thanks for having patience with me!
 
Sorry, not really following that. I think, re-reading your description, where you have AA15:AA24 above you meant to put in AA15:AA18. But still, what cell is this mean't to go in (I don't see 5:30 listed anywhere). And how do we know (how does the formula know) that this person isn't there at 12-5 and also at 11-4?
Pictures are very hard to work with, it means we have to retype all that info on our own sheets to recreate your sheet so we can play with and adjust our formulas.
I know you can't part with "sensitive" info, but you can select the area you are working with (as in your picture) and paste the values and formats into a blank worksheet along with the formula/s that you are having issues with. That way we still only see what you want us too and we can checkout the formulas 1st hand.
Sorry I'm not too much help at the moment.
 
Last edited:
You must log in or register to reply here.
Back
Top