-1

Can I, somehow, based on time stamp and how much is done, estimate time and date for when a job is complete? I got this log that a bot I've made writes in column A time [yyy-mm-dd hh:mm], and in column B it wrights how much is done. It sorts it all with newest on top.

Sodori
  • 31
  • 1
  • 7

2 Answers2

0

Consider:

enter image description here

In this example the time span is 1 hour and the percent complete in the time span is 78%
Cell C1 contains:

=100-B1

the percent remaining to be done.

In cell D1 is the formula:

=(A1-A2)/B1

D1 yields the time required to complete 1%

Finally in E1:

=D1*C1

the time required to complete the work!
(Note...C1,D1,E1 can be combined into a single formula)

EDIT#1

The formats of E1 and D1 were picked to display the relevent parts of the data
The format of D1,E1 is: mm-dd hh:mm:ss.00

Change this format to suit your needs!

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • `=(A1-A2)/(B1-B2)` if previous status was not 0%? – Aprillion Jan 14 '15 at 15:02
  • Thank you! Running into some issues do not know if it is formating or not. What I know is that B1:C1 is formatted as percent (because it's what it has to be), and D1:E1 is formated to "yyyy-mm-dd hh:mm" but what I get then from D1 is: 1900-01-00 01:16 and E1 is: 1900-01-00 00:16. I have copied word for word, but it's not working.. – Sodori Jan 14 '15 at 15:06
  • ah... the end date instead of duration would be `=A1 + D1*C1` – Aprillion Jan 14 '15 at 15:08
  • note the task in your sample data is not supposed to take 1 mont to finish, just the 16 minutes 55 seconds part, `[h]:mm:ss` might be a better suitable format for time diff – Aprillion Jan 14 '15 at 15:17
  • @Aprillion ...............you are correct.....I will search for a better format.............. – Gary's Student Jan 14 '15 at 15:29
0

Gary's answer is correct, but for convenience if the 2nd row of data can be an arbitrary progress status (not just start of task = implicitly 0%), and if you use percentage number format (or decimals) and you would like to get the end date instead of just the duration, use following formula:

=A1+(A1-A2)/(B1-B2)*(1-B1)

formula printscreen

Aprillion
  • 21,510
  • 5
  • 55
  • 89