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
0
Brian8214 (439)
3/4/2005 10:29:08 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
582 Views

Similar Articles

[PageSpeed] 53

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
0
Springbok (23)
3/4/2005 11:21:02 AM
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 <Brian@discussions.microsoft.com> wrote in message
news:1A3041A1-5563-404B-9B76-A36DF51EF933@microsoft.com...
> 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


0
R
3/4/2005 11:24:05 AM
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
0
Kassie (262)
3/4/2005 12:49:12 PM
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
0
Brian8214 (439)
3/4/2005 5:49:04 PM
Reply:

Similar Artilces:

Comment with date stamp 12-09-09
Hello all, I copied these macros from the "Contextures" site but I would like some help. I've already run the this macro: Sub CommentNote() Application.UserName = "Note" End Sub ...but I would like to also have a automatic time stamp in the comment as well. I can run the following macro but the comment only shows the time, not the above "Note" and you have to run the macro for the time to show. To insert a comment with the current date and time, or append the current date and time to an existing comment, use the following macro: ...

CFileDialog -- auto completion issue
I'm using an extended CFileDialog (provides Win2k dialog support) and all is working fine, but I had one minor tweak question. If I have the files: c:\temp\test.xml c:\temp\test.txt And I specify that the dialog is only looking for .xml files, when a user types into the file name field "test", the auto-complete dropdown lists suggests both possibilities. I'd like for it only to suggest the .xml file since that's what the dialog's filter is set to. This works in Office 2000, but with any other app that uses the dialog, you'll get the multiple "incorrect&quo...

Formula for Date
I'm new to formulas and just want to display the current date in my outlook form (e.g., December 18, 2004). What I've done is created a combination text field where I have the following fields: [Email Opening Date] [Full Name] [Job Title] [Company] [Business Address] Dear [Full Name]: When I send a new message, I then copy the values into my email instead of copying the data (name, title, address, salutation) one field at a time. This allows me to personalize the email. The problem is that I do not know what to do to with formulas to show the current date as I note above. Thank...

Create semi-log plot for percentage
Good morning, I have exhausted myself searching the internet for a way to create this graph. I am really hoping that someone on this forum will help me. :confused: I am trying to make a (reverse??) semi-log plot with the y-axis to display 0.9, 9, 99, 99.9, 99.99%. On the x-axis I want concentrations of 0.0, 0.01, 0.05, 0.10, 0.25. I have used the scatter plot with the logarithmic funchtion selected...but that gives me 10,100,1000....I can seem to figure out how to get it to do what I need. Thank you in advance to anyone who replies! galgocid -- galgocid ---------------------------...

Date Format Challenge
Hi Folks - I'm using Excel 2002. Try this challenge in a new blank worksheet: Enter 10:15 in A1 Enter 10:30 in A2 Enter 10:45 in A3 Enter 1 in A4 Excel will automatically treat the 1 as a date. Why? I did not format the column as a date. Or, is Excel looking at the previous entries and assumes I need a date. Thansk. Mike Not for me, if I do that it returns 00:00 in A4 as expected since it thinks I enter times and 1 is 24/24 -- Regards, Peo Sjoblom "Michael" <mlaferriere@homekeyinc.com> wrote in message news:Qo_Kb.29501$JD6.21927@lakeread04... > Hi Folks -...

Auto Complete
I have a user that has the auto complete flag set but when he restarts his machine he has to start all over. Nothing is retained from the day before. I've looked everywhere I know to look. Any ideas? Steve You have? Search this group then. This problem has been posted countless times. It usually means a corrupt Outlook profile. -- Russ Valentine [MVP-Outlook] "GSP@DCS" <info@dcs.com> wrote in message news:2d836$45184a6c$d1a8fa82$5760@EDELTACOM.COM... >I have a user that has the auto complete flag set but when he restarts his >machine he has to start all ove...

Set date format in Template in Excel
Is there a way to set in the template so that all dates entered show as 2/6/10 not as 02/06/2010? Thanks in advance. If it's a template, you could change the format of each of the cells to what you want. falcios wrote: > > Is there a way to set in the template so that all dates entered show as > 2/6/10 not as 02/06/2010? > > Thanks in advance. -- Dave Peterson ...

Adding text and date problem
I have text in column 1, a date in column 2 and in column 3 I wish to show the text followed by the date. I have tried both Concatenate and & but the the result is text followed by the underlying number representing the date. I cannot seem to format this column to show the date as dd/mm/yy. Any suggestions? Regards ="Today is: "&TEXT(A1,"mm/dd/yyyy") -- Kind regards, Niek Otten Microsoft MVP - Excel "newman" <aa111@despammed.com> wrote in message news:464C1646.AC6A42E0@despammed.com... |I have text in column 1, a date in column 2 and in column...

Import dates from TXT
Hi I've got a question about excel I've got a notepad document in TXT format with some information about names and dates I like to import the list in excel now this is my file for example Mikey 10-12-1960 Sandy 15-11-1984 This is the file i'd like to import But when I import it in Excel the dates are not standard reconized as dates How can I make Excel detect that it are dates? How can I make excel make the cel where it places the date automatic transform into a date cell without manually have to change cel properties every time I import ...

Date Range
Hello, I have a table of employee information, which include 2 fields called Start Date and End Date. I want to be able to run a query that enables the user to enter a date range to find those who are between a specific Start Date and End Date. Example: Find those between Sept 1, 2008 (Start Date) and September 30, 2008 (End Date). Thanks, JL You need to be a bit clearer on what you want. I am guessing that you want to match records where the period Start Date to End Date overlaps fully or partially the days in the date range that is input. WHERE FldStartDate <=...

Completely Stumped
Some kind souls help is requested to make my worksheet more user friendly: 1) When data is filled in across cell range B14 � R14 and I then press �enter�.... a new row is automatically inserted either above or below. 2) The new row would retain the formulas in the cells just copied, but none of the calculations or text. I am using using the following code: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim lastrow As Long lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row Rows(lastrow).AutoFill Rows(lastrow).Resize(2), xlFillDefault Rows...

Modify Created On date and Created By fields in 3.0
Hi There! I will soon be importing some paper based legacy data into crm. I will be doing this by creating new records and inputting the data. They will be in the form of leads and opportunities. My problem is that I use several reports that use the “Created On” date to track leads and opportunities. The legacy leads will not be representative of the actual created on date and will skew my reports. I am hoping to find some way to modify the “created on” date in order to make the lead accurate insofar as when it was actually received by the company. Does anyone know any way to mod...

Auto Complete list has been deleted somehow anyone know how to fix??
I have hundreds of emails in auto complete and today when I tried to use it they were all gone. This happened to me about 3 years ago and I never was able to find out why. Can anyone help me please.. Submitted using http://www.outlookforums.com mike long wrote: > I have hundreds of emails in auto complete and today when I tried to ... <gatewayed post was truncated to illustrate lack of proper line wrap> The autocomplete cache is in the nickname file (.nk2). Do you still have a ..nk2 file? If it is gone or corrupted, restore it from your backups. http://support.mi...

RE: Understanding Project's Percent Complete vs. Percent Work Complete
Brian Kennemer wrote the subject-line article several years ago and was very informative/enlightening on how % Complete is computed, etc. However, in this article he showed a Microsoft Project view (Figure A) that had both "Work" and "Actual Work" rows in the detail lines for each resources on a specific task. Where can I find this view. I do need to enter actual hours worked per resource per day on their respective task thanks, David Hamil MaxVision 256-652-4322 David@MaxVision.com Perhaps what you seek is on the resource view or task usage view. Bot...

Auto Complete Category
Hi All. I'm having an issue with Money Plus. When I download my statement, the information that come is: MEMO (i believe this should be the payee and it is going to the wrong place) date and amount. As the information is saved as MEMO and not PAyee, Money doesn't learn to auto complete thye categories according to previous transactions. Does anyone know how to solve this issue? In microsoft.public.money, Mozinha wrote: >Hi All. I'm having an issue with Money Plus. When I download my >statement, the information that come is: MEMO (i believe this should >be the payee and i...

Converting Date in HQ Manager Report
I have modified a PO report to include PurchaseOrder.LastUpdated as a column. Unfortunately the report include the time stamp in the LastUpdated date field. When I "Copy as Table" & paste it to my Excel sheet, the time stamp is also pasted. Even though I formatted my Excel column to Date format 1/1/2001, the time stamp still shows if I were to select on any of the date cells. This messes up my cell formulas in excel. The only way to get around this is to format my the date column in my excel to 1/1/2001, copy the whole column, paste it a notepad, then re- copy it from ...

Weekend dates show on chart, not on spreadsheet
I am collecting data for workdays, so the weekend dates do not show on the spreadsheet. When I create the chart, the weekend dates show. How do I eliminate them from the chart Thanks, Carole - Right click on the chart, choose Chart Type from the pop up menu, and make sure it's a Line chart not a Scatter chart. Then right click again, this time choose Chart Options, and on the Axes tab, change Automatic to Category. Now you'll only have an axis tick where you have a date in your data table. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and...

Uninstall Mendeley completely
Dear Folks, I have accidentally installed Mendeley, which turned out to be a big mistake. I uninstalled it several times after closing all other programs, but now it still appears as "Add-Ins" in office 2007 (including word, excel, etc). It also interferes with Endnote x2. This is a huge problem for me now and please help. -CW -- restlessHK ------------------------------------------------------------------------ restlessHK's Profile: http://forums.techarena.in/members/162019.htm View this thread: http://forums.techarena.in/ms-office-support/1279777.htm ht...

Excel won't completely open
When I click on an Excel document it will start to open but stops at a grey screen. Only a few toolbar commands are selectable, all others are grey and not accesible. I need to go to file to open the correct document. This is frustating as I have several document in Excel and this is also confusing when I email it to someone else. Please help if you think you can. Hi try the following: goto 'Tools-Options-General " and uncheck "Ignore other Applications" Exit Excel and try again If this doesn't work try to re-register Excel 1. Close Excel first and 2. On the Windows...

Show only Journal entries in Delete bin, sort by deletion date
I posted a number of times about the problems I encountered with Ctrl- D (delete a Journal item) being right beside Ctrl-S (saving a journal item). On occasions where I actually notice an accidental deletion, one thing that could help is to be able to view only journal items in the Delete bin. Is there a way to do this? Another thing that might help is to be able to sort by deletion date. Is there a way to do this? Sometimes, I accidentally delete a journal entry from way back, and that basically means its buried in the Delete bin (which is normally sorted by date). Even if the d...

Shortcut key in register by DATE?
In Quicken, I could hit Ctrl-D and automatically go to a specific date in my register. How can that be done in Money 2006? If Ctrl+D doesn't so it--and I've never heard of it--you probably don't do it in M06. "iomag" <iomag@discussions.microsoft.com> wrote in message news:BCEA6349-7BE4-408D-8D8C-F65151AFE2BA@microsoft.com... > In Quicken, I could hit Ctrl-D and automatically go to a specific date in > my > register. How can that be done in Money 2006? "Dick Watson" <littlegreengecko@mind-enufalready-spring.com> wrote in message ...

2007 Structured Reference auto-completion not working completely?
In Excel 2007, when I: 1) Select a cell 2) Type "=" in the formula bar 3) Click a cell in a Table I thought Excel would add a Structured Reference to the formula. Instead, it just inserts an old-style, "A1" reference. The "Formula AutoComplete" and "Use table names in formulas" options are both turned on. When I click the cell in step 3, the TableTools tab appears so Excel agrees that the cell is in a Table. Isn't Excel supposed to insert a Structured Reference when you click a cell in a Table? Is there another setup/configuration switc...

Forms completion
Here is a question for you, we have a pretty simple database to gather comments and suggestions from users for improvement on things around our office, It is a form entry, a suggestion that was given is when users want to enter multiple suggestions they are having to re-enter the header section of the form (Basics of name and contact information) repeatedly, is there a code or work around where that will replicate for the user if they click the add new record after they enter the first entry? Are you storing this information in every row of the table? The way it should work is, when the...

inbox repair tool fails to complete
hi, I've been having problems with my newish laptop XP & Office 2003 ST edition. Tried to run inbox repair tool on the .pst. I get the message that says internal errors were found, and I selected the box for a backup file to be created, but the process hangs on the repair. I tried twice, and can verify the .bak file is there and the same size as the .pst, but the repair won't complete. Using the Task Manager indicates the process is not responding. Happened twice in a row. Any thoughts? Thanks, Carmen Greta Grip <misscarmengetit@hotnospammail.com> wrote: >...

Windows update is not completely successful Error Code: 0x64C
Cannot find a solution anywhere. No such error code in any of the help screens. It is now showing Error Code: 0x8007064C. Still cannot find this discussed anywhere. "John with RadioWorks" wrote: > Cannot find a solution anywhere. No such error code in any of the help screens. "John with RadioWorks" <JohnwithRadioWorks@discussions.microsoft.com> wrote in message news:016D3C6E-B2BE-49CC-8678-580B6E169903@microsoft.com... > It is now showing Error Code: 0x8007064C. Still cannot find this discussed > anywhere. > > "John with RadioWo...