Value and Codes

**LawrenceP** · 12-13-2008, 12:39 PM

I want to assign certin values to codes, such as:

1 has a value of 8 or

the color blue has the value of 8

Is this possible. I am writing a simple work schedule and would like to be able to track total time and over time of my staff. Example below. 1 have a value of 8 hours worked, D has a value of 0 hours worked.

Thanks for the help
LMP

DATE: JAN 1 2 3 4
NAME TH F SA SU

Joe (ft) 1 1 D D
Gene (ft) D 1 1 1

**arthurbr** · 12-13-2008, 01:20 PM

Hi and welcome to the board
Maybe conditional formatting is what you are looking for?

**mewingkitty** · 12-13-2008, 01:23 PM

Excel 2003 or Excel 2007?

**LawrenceP** · 12-13-2008, 01:40 PM

Originally Posted by mewingkitty

Excel 2003 or Excel 2007?

Sorry 2007

**mewingkitty** · 12-13-2008, 01:43 PM

Check out the attached worksheet.
You'd only be entering info in the shaded area.

**LawrenceP** · 12-13-2008, 02:04 PM

Here is the actual templet with break down page. 1 = the first 8 hour shift, 2 = the second shifts 8 hours, third = the third shigts 8 hours, D = day off.
I'm trying to assign values to these shifts:
1= 8 hours
2= 8 hours
3= 8hours
D= 0hours

**mewingkitty** · 12-13-2008, 03:59 PM

so do you want readouts on another sheet listing the 8 or 0 which corresponds to your 1, 2, 3, d..? and what do you want done with your other letter thingies, 2IN, PL, and PHD.?

Oh, and what is your week breakdown, you have 31 days of data, that's not four weeks, just wondering what you want referenced as week 1, week 2, etc. I'm assuming that you want the overtime to be everything over the hours listed next to their names on the "Breakdown" sheet.

On a side note about the OT, I'm guessing that 80 is bi-weekly hours...? I know there's people who work 80 hours in a week, but it's highly unusual.

Edit:
Uploaded template with guesses at what you want.
-You'll have to expand the named list which references hours to define the PHD and 2IN etc., it's on the third sheet, "Sheet 1". Currently those entries are treated as 0.
-Added a row for total regular hours, it's may not be necessary, but was helpful to check calculations, so I left it in.
-Overtime is based on going over 40/week for FT employees, 20/week for PT employees.
-Weeks are calculated from Sunday to Saturday, there are 4.5 weeks worth of data, wasn't sure how to approach that.
-The cake is a lie.

mew!

**LawrenceP** · 12-14-2008, 09:53 AM

mewingkitty: Thank you for the help! If I wanted to set my target overtime at 30 could I edit this formula to read this?
=SUM(IF(C8>30,C8-30,0),IF(D8>30,D8-30,0),IF(E8>30,E8-30,0),IF(F8>30,F8-30,0))

Also how do I go about adding to the reference list? I would like to add in the values for the 1IN,2IN,3IN which will all have values of 4? Could you also explain to me the function of the 3rd sheet? Is this a table of reference for the others?
Lastly, when I go and complete other months what formulas will I have to manipulate to preserve the integrity of the overall workbook.

Thanks again for the help!
LMP

**mewingkitty** · 12-14-2008, 06:17 PM

I'll re-post the worksheet with details on how each part of it works, be up here in a couple of minutes, I'll add it as an edit to this post.

Edit:
Uploaded chart with values changed as requested, and explanation of each part. Just gimme a shout if you have any other questions.

mew!