I have a spreadsheet to track how many KM I am cycling this year. Each day of the year is a separate row. Based on performance so far I am calculating how may days it will take to reach 5000km, this gives me a number such as 285 which is the calculated days it will take to reach 5000km. What I want to do is turn that into the actual date by counting down that number of rows to the 285th row and returning the date held on that row. I'm sure this is easy but I can't find the formula or mix of formulas to do this.
How to locate a row based on its row id held as a value in another cell

jdumas 
August 21, 2021 at 12:22 PM 
Thread is marked as Resolved.



It would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (desensitized if necessary).

Ok. In the attached file F2 is the total KM to date (D234) divided by the number of days so far, so this gives the average KM per day (17.63).
F3 is then the target 5000km divided by the average KM per day to calculate how many days at the current average it will take to do the 5000km (so at present 283 days).
What I would like is to then take the 283 days and convert it into the actual date in the year the 5000km will be completed eg the 283 day in the year which I though would be easiest found by counting down 283 (+1 for the header) rows in column A and returning the value (in this example 10th Oct).

Try this formula in F4:
=DATE(2021, 1, 1) + INT(F3)1

Cheers, thanks for that.


Enter the KM's you rode in Cell F1 of Sheet "Report" and click the button.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!