add and count dates and summarize results

HI

Really need some help with 2 things. I have attached a dummy copy of the sheets involved. I am trying to set up a appraisal workbook that will calculate when appraisals are due based on employees contract date.

New employees will have 3 appraisals in 1 year - 3 mth, 6 mth and then every year after.
Existing employees will only be appraised yearly.

I have entered a formula but it'll only work for new employee's ie; under 3 months. How do I get it not to read if the employee is employeed over a year??? would i be better using dateif formula ??

Also I need to summarize on sheet 2 who is due appraisals in certain mths.

If anyone can help I would be very gratefull

Thanks In advance
C

Hey Jaz,
Thanks so much for reply.

What I need to achieve is that if the date in col E is within the last 12 months then they will need to appraised in 3 mths, 6mths and then 1 year. And if the date in E is not within the last year then the 2011 date based on their contract start ie : started 20/03/2006 then appraisal due 20/03/2011.

Also the second part is to be able to summarize the data on sheet 1. Therfore showing everyone who is due apprasil say in Jan, Feb, March and so on

Thanks hope this is a little clearer

Hi Jazz,

Your interperting skills are very sharp - sheet 1 is exactly how I wanted it - thank you so much.

On sheet 2 ( summary ) what i would like to achieve is a monthly breakdown of anyone that is due an appraisal in a given month. It is not essential that the format of the sheet is as example.

Is there a way to retreive the data for given months so that I could have say a schedule of everyone I need to appraise in Feb, March etc.for instance regardless of wether its a 3 mth, 6 mth or Year. Basically like a monthly planner.

Hope I am being clear

Thanks Again for your help
C

This is not precisely what you described but I think you'll find it meets your needs. The second sheet adds a column giving the date for each employee of the next review date after the current date. The data is autofiltered, and you can sort that column in ascending order to show a schedule of upcoming reviews. They are not grouped by month, but I wasn't sure how important that really was.

Let me know how this works, it can be tuned up.

Hi Jazz,

I had a play around and I think we are nearly there, I just have a couple of queries.,,,.

The Info that is to be entered into B,C,D in 2011-2012 is linked to a Staff File - which is constantly being updated as staff come and go. So when I copied and pasted the link from the staff file into sheet 2011=2012 thats fine and then can sort in summary asscending no problem. When I update the Staff file and the new employee it links 2 things happen.

1) col F shows year appraisal - not 3 month
2) col g also shows 'year appraisal ' - and thus sorts like this

Any Ideas??

Thanks so much for your help you are very generous with your time
C

Originally Posted by cathal78

The Info that is to be entered into B,C,D in 2011-2012 is linked to a Staff File - which is constantly being updated as staff come and go.

Thus the importance of full disclosure when first describing the problem....Now that we are talking about two different files, it would be very helpful if I could see the staff file, and how your other file links to it. Ideally you could just attach both files as you now have them, but you probably want to clear out people's names. Can you do that? I can repopulate it with random fake names.

My apologies on lack of transparency !

3 files attached

Staff Files - data base that is constantly updated with staff leaving etc.( this is the file that contains the source data that links to the other 2 files and the only one that gets ammended.
Attendance Tracker - linked A,B,C,D to staff file
Cathal78 - linked to staff file A,B,C,D,E

Thanks for all
C

Originally Posted by cathal78

When I update the Staff file and the new employee it links 2 things happen.

1) col F shows year appraisal - not 3 month
2) col g also shows 'year appraisal ' - and thus sorts like this

Any Ideas??

When I asked for your workbooks, I wanted to be able to see the same error that you are seeing, but I cannot because STAFF FILES INFO(1).xls has no data. I went ahead and plugged in my own data and started from scratch. I could not reproduce your problem, but after going through the process I think I know what is happening to you.

I assume you are talking about column F and G on the Summary worksheet. Look at the formula in Column A of the row that's giving you trouble. Take note of which row it refers to in "2010=2011". Now move over to columns F and G, and see if they refer to the same row. I'll bet they don't.

The tricky thing about sorting formulas is that they take the row references with them. It starts out with row 2 in Summary referring to row 2 in 2010-2011, row 3 referring to row 3, etc. Then when you sort Summary, now row 2 refers to row 36, row 3 refers to row 104, etc. If you create a new row by copying formulas from the last row, your new row won't refer to the right place.

One workaround to this is to create a dummy row at the top of both sheets with all the formulas that can be the source to copy formulas to a new row in Summary. This dummy row would have to have values that guarantee it will always be the first row of data, and if it is row 3 it will always refer to row 3 on the other sheet. It's easy, but less than elegant, I'll admit.

It may be that my solution turns out to be hard to manage in a dynamic environment. The only thing that would be better would be a VBA solution, where the Summary worksheet is updated on demand by VBA macros. This would take a bit more time and if I can manage it I'll propose something.