Need help with spreadsheet

[stryped]

I have an exel spreadsheet that has a tab for every employee. Each employee has a calnder for January through December of the current year. Example: A:5 through A:35 are the dates 1-1-11 through 1-31-11. H:5 through H:32 are 2-1-11 through 2-28-11 and on and on. I enter a total amount of time missed for family and medical leave. Our policy works on a rolling basis, meaning as of today, 6-17-11, an employee's FMLA that was taken 6-17-10 to 6-17-11 is what is added up toward their 12 week allotment.

To throw in another caveat, I am just now creating this calander system to track employees, so I dont have anything to look back to as of now. My hope is to continue this untill I do get enough information that the "rolling year" would be correct.

Is there a way to have a spot on the spreadsheet to indicate that on the day the spreadsheet is opened, what the persons family and medical leave days have been used? (Automatically rolling back 12 months from the date the spreadhseet is opened?)

I appreciate any help!

[MarvinP]

Hi Stryped and welcome to the forum,
You might Edit your question and change the title to a more specific problem like "How to calculate missed time days"

The design of your workbook may seem logical but Excel doesn't think like that normally.

I'd create a simple list of data with the following columns
1. Employee Name
2. Employee Number (if needed)
3. Date of missed work
4. Hours of work missed

Using these 4 columns you could then create a Pivot Table adding the hours missed for each employee. You could also filter it based on the Date of Today. This way you could automatically only look at the last 12 weeks.

The design of the above data is much easier for excel to deal with and get answers from.

See
http://office.microsoft.com/en-us/ex...001092968.aspx
http://www.csd509j.net/district_info...xcel/lists.pdf

Then start learning Pivot Tables and your job will get a lot easier.
http://faculty.fuqua.duke.edu/~peckl...Intro06-07.pdf

[stryped]

Ok. I am a newbie so forgive the ignormance. Is it as simple as putting every emplyee name in the left column and then the data you want in additional column? It seems like it might be more typing because rather than having the dat automaticaly on the spreadsheet I would have to put the date for every employee that has missed. (We have about 200 employees). I am also tracking points for attendance purposes. on our system, points are tracked on a three month rolling basis. If they exceed three points in three months, they are put on probation then have to go six months without exceeding three points. It would be nice to be notified somehow when a person exceeds this and either goes on probation or exceeds the three points in 6 months while on probabtion. Every where I ask for help I seem to hit a brick wall.

I appreciate any help!

[MarvinP]

Hi stryped,

Look at Validation Lists where you could have a single list for all employees on a separate sheet. You could then have every cell in column A have a dropdown and you would simply click on the Employee Name.
See
http://www.exceluser.com/solutions/q0002/validate.htm

IMHO - a single list with 4 or 5 columns is much easier to deal with than one sheet per employee. You would have 200 sheets?

[stryped]

Hi stryped,

Look at Validation Lists where you could have a single list for all employees on a separate sheet. You could then have every cell in column A have a dropdown and you would simply click on the Employee Name.
See
http://www.exceluser.com/solutions/q0002/validate.htm

IMHO - a single list with 4 or 5 columns is much easier to deal with than one sheet per employee. You would have 200 sheets?

Thanks. This is getting confusing. I agree with what you said about 200 sheets. So, it sounds as if the best thing to do is in the first column have the names of the 200 employees. Then, the other columns will have other information. This is where I am having problems. I need the following information:

1. I need to input the date of an absence for each employee. (Sometimes one absense is multiple dates. Example: employee is out 6-16-11 through 6-18-11).

2. I need a column to assign a point value for each absence. (1 point for absence, .5 point for absence with doctor's excuse, 1.5 for absence without call in ect.

3. I need one column each for FMLA, Jury Duty, Funeral Leave, and workers comp.

4. I need a way at an instance to be able to go back a rolling three months to look at an employee's attendance point total. (rolling not calander.)

5. I need a way to go back a rolling 12 months to look at an employees FMLA used.

I think I am understanding more from the replies I have received, but can anyone help me with what I need for the rolling time period and how I would set up the spreadsheet?

I am sorry for all the basic questions but I do appreciate the advice!

[MarvinP]

Hi stryped,

Find the example where I've created lists for validation and a pivot table. You can also use the auto filters to filter individual employees or dates.

Hope this example helps to show you what I was thinking.

Also Excel 2007 and 2010 are better with pivot tables than 2003.

[stryped]

Hi stryped,

Find the example where I've created lists for validation and a pivot table. You can also use the auto filters to filter individual employees or dates.

Hope this example helps to show you what I was thinking.

Also Excel 2007 and 2010 are better with pivot tables than 2003.

Thanks so much. That may be one problem, I have 2003. The table gives me a message when I read it to open it in the version of Excell it was created in.

[MarvinP]

It should open and you can see what I was doing, even with 2003.

[stryped]

It should open and you can see what I was doing, even with 2003.

It opened. It was when I clicked on the drop down menu for the side table that said "sum of label" and "total of points"

I like this. But how do I go back at a glance to see what someone's point total is 3 months from when I look at it or 12 months for FMLA?

[angelam090]

I would like to create a spreadsheet that tracks number of points employees for being absent, tardy, leaving early. Every occurance equals a point. After 60 days points will roll off. I am looking for spreadsheet that will count points and rolloff 3 points after receiving no occurances for 60 consecutive days.

[arlu1201]

angelam090,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.