SOLVED

# Calculate days remaining in a task based on start date and percentage done.

Copper Contributor

# Calculate days remaining in a task based on start date and percentage done.

Hi, I've searched for a solution for this but I'm struggling, possibly in part due to brain fog!

I'm part of a big migration project, and I have a formula that works out how far along the project is (C100) and a start date of 6th Feb 2023.

The percentage completed figure in C100 fluctuates based on new data coming in and the complexity of the work (it just jumped from 87% to 61%!), so I know it's not going to be accurate, but can someone help with with a formula that works out the number of days remaining until the task is completed based on the start date and the percentage done?

9 Replies
best response confirmed by Kirk-Anderson (Copper Contributor)
Solution

# Re: Calculate days remaining in a task based on start date and percentage done.

Let's say the start date (6th Feb 2023) is in D100.

The end date is returned by

=D100+100%/C100*(TODAY()-D100)

Format the cell with the formula as a date.

# Re: Calculate days remaining in a task based on start date and percentage done.

Awesome thank you

# Re: Calculate days remaining in a task based on start date and percentage done.

Follow up question, our project (migration of data between 2 health systems with a 3 month gap) is generating a lot of issues, which the spreadsheet keeps a track of, these will be dealt with one the main project gets to 100%. This figure is a number, not a percentage, is there a similar formula you could come up with that would estimate completion date if the start date is in C103 and the number of issues is in C101? Thanks in advance.

# Re: Calculate days remaining in a task based on start date and percentage done.

You have the start date in C103 and the number of issues in C101. What other info can we use? The number of issues resolved per day? The number of issues has been resolved up to today? Something else?

# Re: Calculate days remaining in a task based on start date and percentage done.

I could calculate the number of completed migration tasks as opposed to ones that generated issues, as it's either or from a pick list. Would that do it?

# Re: Calculate days remaining in a task based on start date and percentage done.

I'm confused now. Could you provide an example of how you think the calculation should work (not an actual formula, but a description)?

# Re: Calculate days remaining in a task based on start date and percentage done.

When you asked what other info can we use, I can give you your second option, the number of issues resolved up to today

So we have the start date in C103, the number of issues resolved in C106 and the number of outstanding issues in C107. Sorry for any confusion. Happy to be told it's not doable.

# Re: Calculate days remaining in a task based on start date and percentage done.

Oh and we're looking for an estimated completion date based on this info as before.

# Re: Calculate days remaining in a task based on start date and percentage done.

Try this:
=C103+(C106+C107)/C106*(TODAY()-C103)
1 best response

Accepted Solutions
best response confirmed by Kirk-Anderson (Copper Contributor)
Solution

# Re: Calculate days remaining in a task based on start date and percentage done.

Let's say the start date (6th Feb 2023) is in D100.

The end date is returned by

=D100+100%/C100*(TODAY()-D100)

Format the cell with the formula as a date.