what formula/syntax needed to automatically insert a date?

[Neal_Const_Planner]

Re-posted due to thread title needing to be changed...

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 on the columns for measuring daily progress. Hence the final day of progress input ='s the day on which the task is completed.

Thus 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 AA11 (where I have the date of 02/07/2018) to AU11 (where I have the date of 22/07/2018) 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%. This 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 know 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 to 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 G5.

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 appreciated,

Neal

[FDibbins]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[6StringJazzer]

Please do not create a new thread just to change the title. The post from AliGW in your first thread explains how to update the title, and it appears that you had done so.

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.