Update cell with various dates

Question

Windows 10Microsoft 365Excel file sampleI&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; J&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; K&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; L&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NReviewsRequiredReviewsCompletedReviewStillNeededDateCompletedStartEnd421-411/3/20211/1/202012/31/2050&nbsp;&nbsp;Is it possible to input various dates in column L?&nbsp; My date range is in Columns M and N for column L.If I enter 1/3/2021; it counts reviews completed correctly.&nbsp; I would like a formula to increase column J each time a different date is entered in column L.Current formulas: Col J = COUNTIF(L2,"&gt;"&amp;DATE(2020,1,1))&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Col K = J2-I2&nbsp;&nbsp;&nbsp;&nbsp;Expected resultReviewsRequiredReviewsCompletedReviewStillNeededDateCompletedStartEnd422-412/3/20211/1/202012/31/2050&nbsp;&nbsp;

mathetes · Answer

kJohn985&nbsp;&nbsp;I'm (fairly) sure that what you're asking can be done, and (fairly) sure that it can be done fairly easily.&nbsp;&nbsp;HOWEVER, it's not altogether clear what you want to be done, or what the context is.For example:given the date range of thirty years (!!!!; is that real?), what kind of reviews are we talking about?why are there only 42 reviews expected over that thirty year period?are they on a schedule?do you really want the number still needed to go down by one each time a new date is entered?what does the entry of a new date represent (given the thirty years)?&nbsp;etc.&nbsp;These are the kinds of questions we'd be reviewing if we were meeting face-to-face. You may think the answers are obvious (or that the questions are impertinent; "just tell me how to do it!")...but the design of a spreadsheet to accomplish desired results does, to a very great extent, depend on what those results are, how well the full process has been explained and understood.&nbsp;So if you can humor me, I think others who might desire to help would appreciate a more complete description. If you can, without revealing any proprietary or confidential information, also post a copy of the actual spreadsheet, that would be helpful as well.

kjohn985 · Answer

given the date range of thirty years (!!!!; is that real?), what kind of reviews are we talking about? Performance reviews based on rating period of employee
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.

mathetes · Answer

kJohn985&nbsp;&nbsp;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.&nbsp;Also, I'd asked if it would be possible for you to attach a copy of your actual spreadsheet. Is it?

kjohn985 · Answer

mathetes&nbsp;&nbsp;

kjohn985 · Answer

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. EtcAnswers: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/20212. 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/20213. 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 L5. The entry of a new date represents the date the review was completed and should be counted in column J6. If I completed reviews on 1/2/21, 2/14/21, 4/14/21 dates.I would like to enter that date in column LThen have it count toward reviews completed in column JNext, reduce the reviews still needed in column K.

Forum Discussion

Update cell with various dates

6 Replies

Resources

ReviewsRequired	ReviewsCompleted	ReviewStillNeeded	DateCompleted	Start	End
42	1	-41	1/3/2021	1/1/2020	12/31/2050