Forum Discussion
Update cell with various dates
why are there only 42 reviews expected over that thirty year period?
are they on a schedule? each employee has a certain number of reviews needed during the rating period
do you really want the number still needed to go down by one each time a new date is entered? Yes, would like to know the reviews still needed
what does the entry of a new date represent (given the thirty years)? date the review was completed during the year.
Thanks so much for you help.
Well, I appreciate your answers to the questions. You missed the last one--"Etc."--which was actually meant to indicate that the preceding questions were just scratching the surface.
Also, I'd asked if it would be possible for you to attach a copy of your actual spreadsheet. Is it?
- Mathetes, hopefully I provided what is needed this time.
1. Given the date range of thirty years (!!!; is that real?), what kind of reviews are we talking about?
2. Why are there only 42 reviews expected over that thirty year period?
3. Are they on a schedule?
4. Do you really want the number still needed to go down by one each time a new date is entered?
5. What does the entry of a new date represent (given the thirty years)?
6. Etc
Answers:
1. I am trying to created a performance review dashboard. Each employees has a rating period based on the last digit of the SSN. For example, 0 – 01/01/2020 to 12/31/2021
2. Based on the rating period each employee has a specific number of ratings to be completed during rating period. For example, Lead Case Advocate need 42 QERL reviews completed during 01/01/2020 to 12/31/2021
3. Yes, each employee has a different rating period based on the last digit of their SSN.
4. Yes, I need to know the Reviewstillneeded column K to be reduced each time a new date is entered in column L
5. The entry of a new date represents the date the review was completed and should be counted in column J
6. If I completed reviews on 1/2/21, 2/14/21, 4/14/21 dates.
I would like to enter that date in column L
Then have it count toward reviews completed in column J
Next, reduce the reviews still needed in column K.It's still not really clear what it is that you want to do here, but what I'm sending back is one way that can at least illustrate a possibility for you. In this case I've created a randomly generated set of dates in a hypothetical "history db" showing dates connected to the first several "names". Because I'm using RANDBETWEEN to generate those dates, it will generate a new set of dates each time you use function key F9 (recalculate).....
And then in column J we count the number of dates associated with that employee; column K subtracts that number from the number expected; column L shows the last date (the highest date) associated with that employee.
You need something like the history of all dates for any given employee in order to have the count change for each time a new date is entered.
For help in understanding how FILTER works, which is a new function at the heart of this potential solution--and which requires the most recent release of Excel to work--please refer to this excellent YouTube video.
https://www.youtube.com/watch?v=9I9DtFOVPIg