Jul 14 2021 06:09 AM
Windows 10
Microsoft 365
Excel file sample
I J K L M N
ReviewsRequired | ReviewsCompleted | ReviewStillNeeded | DateCompleted | Start | End |
42 | 1 | -41 | 1/3/2021 | 1/1/2020 | 12/31/2050 |
Current formulas: Col J = COUNTIF(L2,">"&DATE(2020,1,1))
Col K = J2-I2
|
Expected result
ReviewsRequired | ReviewsCompleted | ReviewStillNeeded | DateCompleted | Start | End |
42 | 2 | -41 | 2/3/2021 | 1/1/2020 | 12/31/2050 |
Jul 16 2021 06:25 AM
I'm (fairly) sure that what you're asking can be done, and (fairly) sure that it can be done fairly easily.
HOWEVER, it's not altogether clear what you want to be done, or what the context is.
For example:
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.
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.
Jul 20 2021 05:10 AM
Jul 20 2021 10:45 AM
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?
Jul 24 2021 08:44 AM
Jul 25 2021 02:33 PM
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