Formula/Syntax required to automatically insert date from percentage series of data

I have a spreadsheet which records progress day by day for a 3 week shutdown project.

Progress is entered on a daily basis for each individual task as a percentage until that task is complete.

Some tasks are completed in one day, others may take multiple days and hence have different finish days.


I also have a column for summarising the %age complete, which simply adds up each cell with the daily percentages entered into them. Of course once a task reaches 100%, either cumulatively or on a single day, the %age column shows 100% and no more progress is entered into the columns/cells for measuring daily progress. Hence the final day of progress input ='s the day on which the task is completed.

Problem is that I have many different tasks, c. 2,500 which reach 100% on different days.

I would like to go back retrospectively and record the day on which each task completed. I could do this by writing a formula to reflect the following:

when sum of progress = 1 in the horizontal series of cells from AA to AU (Note that AA2:AU2 is where I have the date of 02/07/2018 to 22/07/201), enter date from cell $1?? where ?? would correspond to the the column reference for the appropriate date

e.g.

Project starts on 02/07/2018 and lasts until 22/07/2018.

Let's say Task 1 starts on 2nd July and finishes on the 11th.

I enter a series of progress updates say day 1 = 0.1 (=10%), day 2 = 0.2, day 3 = 0.1, day 4 = 0.1, day 5 = .05 day 6 & 7 = weekend = 0.0, day 8 = 0.2, day 9 = 0.2, day 10 = 0.05

So on day 10 I have achieved 100%. Thius now means that the sum of the cells AA5 to AJ5 = 1 which ='s 100%, . The date is in row 2 cell ref AJ$2. So I now want to copy that date and enter it into the column recording completion dates which would be say G5.

I don't have a clue where to start in terms of selecting perhaps a logical statement or writing the correct syntax tro achieve this, which surely must be possible?

My alternative is to go through all 2,500 tasks, identify the point at which progress = 100% and manually enter the appropriate date into column G cells.

One way I'm thinking is that if I have a start and finish of a series of inputs, and the finish is recognisable by the point at which the last numerical entry >0 is entered, then if I could write a formula to identify this cell and then capture the date from the cell ref ??$2 this might be possible.

Any help most apprciated,

Neal