Help! Trying to Create A Staffing Sheet...

[matt.wolst]

Hello, I need to create a staffing sheet for work. I have attached an idea of how it would like it. Although, this will be needed for about 20 people in total.

Please can you tell me how/or whether it is possible for the following to happen:

1) When I type peoples hours in using the 'in' and 'out' columns, I want the time columns for their row to automatically fill in a white colour between those times they are in and have the numerical value of 1 across the hours they are in work.

2) Depending on peoples hours, people are entitled to different break times. eg, 15 mins, 45 mins, 75mins. When I type in peoples hours, can I get it so in the break column it automatically works out what break they are entitled to. eg 4 hours or less = 0 break, 4-6 hours=15mins 6-9 hours =60mins 9-12 hours = 75mins, so when I type in Joe Bloggs 16:00 - 22:00, it will automatically work out he has 15min break to take.

This would be a great help if you can tell me the general formula of how to do these!, and where to type them!

Thank you in advance!

[matt.wolst]

Also just for clarification, the 'confirm' column is for the staff to tick manually using a pen, and is not part of any formula.

[jake.masters]

Yes all of those things are possible, but I would suspect people on this forum would be more inclined to help you if you had a more specific question.

The breaks can be calculated using nested IF formulas which will be able to figure out based on hours how much break one should receive.

Here is some help on nested IF functions:
http://www.techonthenet.com/excel/fo.../if_nested.php

[jake.masters]

The function for your break calculation would look something like this:

=IF(AND(TIME>=0,TIME<4),0,IF(AND(TIME>=4,TIME<=6),15, IF(AND(TIME>=6,TIME<=9),60, IF(AND(TIME>=9,TIME<=12),75,""))))

You would need a column calculating each time period each employee has been working for this to work.

[matt.wolst]

The function for your break calculation would look something like this:

=IF(AND(TIME>=0,TIME<4),0,IF(AND(TIME>=4,TIME<=6),15, IF(AND(TIME>=6,TIME<=9),60, IF(AND(TIME>=9,TIME<=12),75,""))))

You would need a column calculating each time period each employee has been working for this to work.

That's great thanks! Would that column not be my 'in' and 'out' columns?

[jake.masters]

You need to subtract out from in to be able to fill those into a bin.

For example, if someone works from 8:00 to 17:00, that formula will not understand what you are asking, because it will think 8:00 is the hours he worked rather than the time he started work. You can either modify the formula to subtract the times, or create a separate column which subtracts the end time from the start time to get the aggregate work time. Then link the formula to that.


Edit:
* You could also just replace every instance of TIME with the location of the end time cell minus the start time cell:

IF(AND(B2-A1>=0...ect

[matt.wolst]

Wouldn't work for me? I'm not great at excel! What formula do I need for example if it was Zoe Ball (on the attachment) and I needed the break to show up on cell E3.

I really do appreciate your help, I'm sorry to be a pain, i'm just not great at this!

[jake.masters]

See attached spreadsheet. Column C is formatted as a number, but is returned as a fraction of a day(12 hours returns .5 in excel), so each value is multiplied by 24.

[jake.masters]

If people are working overnight, you may want to include a date in the calculation as well as a time, otherwise excel will return negative values.

[matt.wolst]

I'm not sure if it's going to be possible. I tried altering it, so the start time would be first, but the hours go negative, and then the formula won't work saying 'Durations can't be compared to other data types'.

[jake.masters]

Switch the formula in C to go the other way around. Instead of =(A2-B2)*24, put =(B2-A2)*24.

[matt.wolst]

Thank you soooooooooo much! Seriously, what a great help!

I don't suppose you know how to change the colour/font of the cells when they are in between times. This is so people can clearly see when they arein, and where the staffing is spread out throughout the day, and will go towards an hourly total of staffing levels.

Ideally, I need to add these cells up at the bottom, so I can say between 16:00 - 17:00 there are 14 people working, at 21:00-22:00 10 people are working., so ideally, they would need a value of 1. I could just change the font so it blends white, so they can't see 1 when it's printed. My main concern is getting them to change colour and automatically adding the value of 1 to each cell. I would also need to change some cells manually after for breaks eg, 0.75, or .50 depending on their break length for accurate hourly totals.

So for example, I type in one persons hours 10:00-16:00, their break automatically appears (already achieved - thank you ), the boxes representing the hours are turned from default grey to white, and their is some numerical value for each hour they are in which can be added to the total at the bottom. I have done a screen cap which I have attached so you get an idea.

Once again, thank you! Hope you can help, but I understand if you've had enough! ha.

Much appreciated!!!

Screen Shot 2012-10-01 at 22.58.03.png