staffing guide

I am a drycleaner with 4 stores

I developed a spreadsheet program to determine my labor hours needed at all of my stores.
Each store’s labor hours is based on its own history and the timing of when a customer comes in. I took the information from my customer invoices file for one year (Oct 1 ’07 to Oct 1, ‘08). I imported the information from those customer invoices with the date, time, amount, and piece count into an Excel spreadsheet. I then massaged the data and came up with the following information.

I did this for each, my plant counter and dry stores: below is the print out of one store. I give it to my manager a week before the new month so the manager can prepare a schedule for staffing. You can plainly see it tells the manager exactly how to staff the store.

If the manager would look at this chart the manager would see a big fluctuation in hours, for example, Saturday between 1pm and 4pm. If the manager would fill that time, the manager would save the company on that labor. (see attached) top one

What I'm trying to do now is compair the budget hours with the actual and find out the difference.

Not sure at all how to do it
I'm using the hours format in excel

So I need if and statements
any help please

Not sure what you're asking for... can you clarify the question?

if I have a timecard spreadsheet and the time is let say
07:02
11:15
Meaning the person punched in at 07:02 am and left at 11:15am

I want in another area of the sheet to show
7am 1
8am 1
9am 1
10am 1
11am 1.25
12pm 0
1pm 0
2 pm 0
ect.......

I want it to look at the range 07:02am to 11:15am and
account for 1 hour in the each 1hour time frame

I tried if(and statements I tried if(or

Hope that helps

what kind of tolerance are you looking for? 11:15 to 1.25 hours is precise but 7:02 - 8:00 is not quite one hour...

Originally Posted by Cheeky Charlie

what kind of tolerance are you looking for? 11:15 to 1.25 hours is precise but 7:02 - 8:00 is not quite one hour...

I'd like to do everything in military time to the minute
so this would really show 1:15

i've attached soemthings I've tried
I highlighted them in yellow

try this, might give you some ideas

CC

Hey there,

My first formula had the right principle but slightly flawed implementation . The upper bound must apply to the end of each time window. See this replcaement for H2:

=MAX(MIN(TIME(1,0,0),$B$3-C2,C3-$B$2),0)


HTH