Completion Percentage of a date range

I have started a task sheet in excel and i have a "task description" coloumn,
a "start date", "finish date", "number of days" and a "% complete" column.

What i want to achieve is the user to enter the first task start date and
the number of days it will take to complete. Then excel will work out the end
date and the percent complete field relating to the current date.

Is there a easy formula of working out the percentage?

Many thanks

Hi Brian,

I think this is right?

Assume Column A is Task, B is Start Date, C is No. of Days, D is End Date
and E is % Complete.

D2=B2+C2
E2==IF((TODAY()-B2)/C2>1,1,IF((TODAY()-B2)/C2<0,0,(TODAY()-B2)/C2))

E2 should be formatted as %.

Give it a crack and let me know.

Cheers,
Jon

"Brian" wrote:

> I have started a task sheet in excel and i have a "task description" coloumn,
> a "start date", "finish date", "number of days" and a "% complete" column.
>
> What i want to achieve is the user to enter the first task start date and
> the number of days it will take to complete. Then excel will work out the end
> date and the percent complete field relating to the current date.
>
> Is there a easy formula of working out the percentage?
>
> Many thanks

A2 task description
B2 start date
C2 finish date
D2 no. of days
E2 expected completion


fill B2 and D2
C2 =B2+D2
E2 =(TODAY()-B2)/20

if Bs and Ds are filled you drag the formulas in C2 and E2 down
format B and C columns as one of the date formats
foramt E column as percenage

is this what you want.
e.g.
A B C D
E
task start finish no. of days %
completion
a 01-Mar-05 21-Mar-05 20
15.00%


--
remove $$$ from email addresss to send email

====================

Brian <[email protected]> wrote in message
news:[email protected]...
> I have started a task sheet in excel and i have a "task description"
coloumn,
> a "start date", "finish date", "number of days" and a "% complete" column.
>
> What i want to achieve is the user to enter the first task start date and
> the number of days it will take to complete. Then excel will work out the
end
> date and the percent complete field relating to the current date.
>
> Is there a easy formula of working out the percentage?
>
> Many thanks

You have already decided on your columns. Keep to that. To determine the
end date, use the formula B2+D2. To calculate %completed, use this:
=((NOW()-B2)*100)/D2, otherwise you will always get 15%


"Brian" wrote:

> I have started a task sheet in excel and i have a "task description" coloumn,
> a "start date", "finish date", "number of days" and a "% complete" column.
>
> What i want to achieve is the user to enter the first task start date and
> the number of days it will take to complete. Then excel will work out the end
> date and the percent complete field relating to the current date.
>
> Is there a easy formula of working out the percentage?
>
> Many thanks

This work well. Thank you very much

"Springbok" wrote:

> Hi Brian,
>
> I think this is right?
>
> Assume Column A is Task, B is Start Date, C is No. of Days, D is End Date
> and E is % Complete.
>
> D2=B2+C2
> E2==IF((TODAY()-B2)/C2>1,1,IF((TODAY()-B2)/C2<0,0,(TODAY()-B2)/C2))
>
> E2 should be formatted as %.
>
> Give it a crack and let me know.
>
> Cheers,
> Jon
>
> "Brian" wrote:
>
> > I have started a task sheet in excel and i have a "task description" coloumn,
> > a "start date", "finish date", "number of days" and a "% complete" column.
> >
> > What i want to achieve is the user to enter the first task start date and
> > the number of days it will take to complete. Then excel will work out the end
> > date and the percent complete field relating to the current date.
> >
> > Is there a easy formula of working out the percentage?
> >
> > Many thanks

Hello Jon,

I was stuck in this problem for an hour and then I found your solution. Thank you a ton for saving me. It works perfectly on Excel 2019

Take care

Adib