Suggestion to improve this work schedule

[spacecaptainsuperguy]

Hi all,

I've been working on a schedule for our department heads to begin using so that we have consistency across the company and I'm surprised by the lack of sophistication and/or flexibility of templates available online. Needless to say, I decided to design my own and I've run into a few snags that I would love to have some input on.

I'm not sure if it's ok to do so, but you can download the work in progress at:
http://www.4shared.com/document/3m9KUqmc/Test_Schedule_Mock_Up.html

I will warn that there are some macros in the file, however you can disable them. They are not part of the questions I'm looking to resolve.

The first question (and the least important) is the inability to show on the schedule when someone is out on vacation. I've got the cells for In & Out set to validate that they are in time format so that the hours and dollars formulas will calculate correctly and because of that, you can't type in "vacation" in the cells. I don't know if there is a way have it both ways.

The second question (and more important) is its current inability to factor in lunches. If I'm scheduled to work from 8-5, it will calculate 9 hours, even though I only worked 8. If every employee or department took lunches then it wouldn't be an issue, but my own department is a perfect example of some employees leave for lunch every day and some don't.

All of my users are Excel novices, so I have to make sure everything is either set up to be fool proof or lock things down so things don't get "F'd" up. The goal is to have my HR person take everyone's departmental schedule and compile it into one big schedule to give to the company president, hence the need for consistency across the board.

For what it's worth, I'm using Excel 2007, but almost all other employees are on a previous version.

Any ideas or advice on either of these or anything that can make the spreadsheet better, I greatly appreciate.

Thanks in advance
SpaceCaptainSuperGuy

 

[gehusi]

Solved - see pm.

 

[ukmikeb]

gehusi

Would you like to share your solution with the rest of the board?

There may be other members with a similar problem who could benefit from the solution.

Indeed, there may be other members who may be able to provide a better alternative solution.

 

[spacecaptainsuperguy]

Here is Gehusi's suggestions on improving the file.
http://www.4shared.com/document/FjsC0OOd/Copy_of_Test_Schedule_Mock_Up_.html

I still need to go through it and when/if I ever get to a finished version, I'll make sure to share it here. If anyone else has some suggestions feel free to keep contributing.

Thanks
SpaceCap

 

[ukmikeb]

Hi

Let me make a further suggestion -

There are many users on this site who will NOT download sheets from sharing sites or who by virtue of the security in place on their connection to the Internet who are barred from accessing those sites.

As a result the response you will get towards solving your problem will be greatly reduced (vis 4 and 1 downloads respectively).

You could use the utilities mentioned in my signature to post samples of your sheet to get the response you deserve or anticipate.

hth

 

[spacecaptainsuperguy]

Thanks for the suggestion ukmikeb. Here is a chunk of the original spreadsheet. Obviously the formatting shown is not quite as pretty as in the file, and the macros built in allow for changing to various custom views, such as "Schedule view" which just shows the schedule columns (for what you'd want to print and distribute to employees). Anyway...I hope this helps generate some additional feedback.

I'll also mention that one suggestion that gehusi made, that I've also considered, is adding columns to calculate the hours for each day so that the formula in the labor column is not having to calculate them and making it quite so complex (and lengthy).

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Pro Shop Schedule
2			Schedule		Actual		Schedule	Actual	Schedule		Actual		Schedule
3		Pay	In	Out	In	Out	Daily	Daily	In	Out	In	Out	Daily
4		Rate	Monday Jun 20		Monday Jun 20		Labor $	Labor $	Tuesday Jun 21		Tuesday Jun 21		Labor $
5	Michael	$ 10.00	7:30 AM	4:30 PM			90.00	0.00	7:30 AM	4:30 PM			90.00
6	Le Ann	$ 10.00					0.00	0.00					0.00
7	Ashley V.	$ 10.00	8:00 AM	11:00 AM			30.00	0.00	8:00 AM	11:00 AM			30.00
8	Ashley V.	$ 10.00	12:00 PM	5:00 PM			50.00	0.00	12:00 PM	5:00 PM			50.00
9							0.00	0.00					0.00
10	Total For Dept		17:00		0:00		$170.00	$0.00	17:00		0:00		$170.00
Sheet1

Excel 2007

Cell Formulas
Range	Formula
E4	=+C4
E10	=SUM(F5:F9)-SUM(E5:E9)
I4	=+C4+1
I10	=SUM(J5:J9)-SUM(I5:I9)
K4	=+I4
K10	=SUM(L5:L9)-SUM(K5:K9)
C10	=SUM(D5:D9)-SUM(C5:C9)
G5	=((HOUR(D5-C5))+(MINUTE(D5-C5)/60))*$B5
G6	=((HOUR(D6-C6))+(MINUTE(D6-C6)/60))*$B6
G7	=((HOUR(D7-C7))+(MINUTE(D7-C7)/60))*$B7
G8	=((HOUR(D8-C8))+(MINUTE(D8-C8)/60))*$B8
G9	=((HOUR(D9-C9))+(MINUTE(D9-C9)/60))*$B9
G10	=SUM(G5:G9)
H5	=((HOUR(F5-E5))+(MINUTE(F5-E5)/60))*$B5
H6	=((HOUR(F6-E6))+(MINUTE(F6-E6)/60))*$B6
H7	=((HOUR(F7-E7))+(MINUTE(F7-E7)/60))*$B7
H8	=((HOUR(F8-E8))+(MINUTE(F8-E8)/60))*$B8
H9	=((HOUR(F9-E9))+(MINUTE(F9-E9)/60))*$B9
H10	=SUM(H5:H9)
M5	=IF(HOUR(J5-I5)+HOUR(D5-C5)+(MINUTE(J5-I5)+MINUTE(D5-C5))/60<=40,(HOUR(J5-I5)+(MINUTE(J5-I5)/60))*$B5,IF(HOUR(D5-C5)+(MINUTE(D5-C5)/60)>40,(HOUR(J5-I5)+(MINUTE(J5-I5)/60))*(1.5*$B5),(HOUR(J5-I5)+HOUR(D5-C5)+((MINUTE(J5-I5)+MINUTE(D5-C5))/60)-40)*(0.5*$B5)+(HOUR(J5-I5)+(MINUTE(J5-I5)/60))*$B5))
M6	=IF(HOUR(J6-I6)+HOUR(D6-C6)+(MINUTE(J6-I6)+MINUTE(D6-C6))/60<=40,(HOUR(J6-I6)+(MINUTE(J6-I6)/60))*$B6,IF(HOUR(D6-C6)+(MINUTE(D6-C6)/60)>40,(HOUR(J6-I6)+(MINUTE(J6-I6)/60))*(1.5*$B6),(HOUR(J6-I6)+HOUR(D6-C6)+((MINUTE(J6-I6)+MINUTE(D6-C6))/60)-40)*(0.5*$B6)+(HOUR(J6-I6)+(MINUTE(J6-I6)/60))*$B6))
M7	=IF(HOUR(J7-I7)+HOUR(D7-C7)+(MINUTE(J7-I7)+MINUTE(D7-C7))/60<=40,(HOUR(J7-I7)+(MINUTE(J7-I7)/60))*$B7,IF(HOUR(D7-C7)+(MINUTE(D7-C7)/60)>40,(HOUR(J7-I7)+(MINUTE(J7-I7)/60))*(1.5*$B7),(HOUR(J7-I7)+HOUR(D7-C7)+((MINUTE(J7-I7)+MINUTE(D7-C7))/60)-40)*(0.5*$B7)+(HOUR(J7-I7)+(MINUTE(J7-I7)/60))*$B7))
M8	=IF(HOUR(J8-I8)+HOUR(D8-C8)+(MINUTE(J8-I8)+MINUTE(D8-C8))/60<=40,(HOUR(J8-I8)+(MINUTE(J8-I8)/60))*$B8,IF(HOUR(D8-C8)+(MINUTE(D8-C8)/60)>40,(HOUR(J8-I8)+(MINUTE(J8-I8)/60))*(1.5*$B8),(HOUR(J8-I8)+HOUR(D8-C8)+((MINUTE(J8-I8)+MINUTE(D8-C8))/60)-40)*(0.5*$B8)+(HOUR(J8-I8)+(MINUTE(J8-I8)/60))*$B8))
M9	=IF(HOUR(J9-I9)+HOUR(D9-C9)+(MINUTE(J9-I9)+MINUTE(D9-C9))/60<=40,(HOUR(J9-I9)+(MINUTE(J9-I9)/60))*$B9,IF(HOUR(D9-C9)+(MINUTE(D9-C9)/60)>40,(HOUR(J9-I9)+(MINUTE(J9-I9)/60))*(1.5*$B9),(HOUR(J9-I9)+HOUR(D9-C9)+((MINUTE(J9-I9)+MINUTE(D9-C9))/60)-40)*(0.5*$B9)+(HOUR(J9-I9)+(MINUTE(J9-I9)/60))*$B9))
M10	=SUM(M5:M9)