Count Days until Completion

Is there a way to count days (with a date enter in Start Column) until 
"Completed" or "Cancel" is entered in my Status Column?

Right now, it is calculating on the Start and Completion Date.  If the 
completion date is blank - I receive wrong date in the duration column.

Start Date:  4/1/10
Completion Date:  4/5/10
Status:  Completed
Duration:  4


-- 
pls
0
Utf
4/5/2010 1:21:01 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
760 Views

Similar Articles

[PageSpeed] 1

Let's say your stuff is in A1:B4 with labels  in A;
In B4 to compute duration we could use any of these
A) =IF(B2>0, (B2-B1),"")  or=IF(B2, (B2-B1),"")  since a positive value in 
B2 will be treated as TRUE
B) =IF(OR(B3="Completed",B3="Cancel"), B2-B1, "")
C) =IF(LEFT(B3)="C",B2-B1,"")
best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Pat" <Pat@discussions.microsoft.com> wrote in message 
news:FC759A34-D4F3-4DE0-ADC1-B96888B898A5@microsoft.com...
> Is there a way to count days (with a date enter in Start Column) until
> "Completed" or "Cancel" is entered in my Status Column?
>
> Right now, it is calculating on the Start and Completion Date.  If the
> completion date is blank - I receive wrong date in the duration column.
>
> Start Date:  4/1/10
> Completion Date:  4/5/10
> Status:  Completed
> Duration:  4
>
>
> -- 
> pls 

0
Bernard
4/5/2010 1:36:39 PM
  Pat penned:
> Is there a way to count days (with a date enter in Start Column) until
> "Completed" or "Cancel" is entered in my Status Column?

> Right now, it is calculating on the Start and Completion Date.  If the
> completion date is blank - I receive wrong date in the duration column.

> Start Date:  4/1/10
> Completion Date:  4/5/10
> Status:  Completed
> Duration:  4


If the start date is in A1 the completed Dated in B1 in C1 put

=IF(OR(B1="",B1="cancel"),"",B1-A1)

and format C1 as a number

-- 
Steve

I think age is a very high price to pay for maturity.
- Tom Stoppard
0
Steve
4/5/2010 1:45:37 PM
Reply:

Similar Artilces:

Count of user's Outlook Top Level Folders
Hello, Anyone know how I can get a count of items on all user's Top Level Folders i.e. Inbox,Calendar,Sent Items,Contacts ,Deleted Items? I am trying to get an idea of which users may be exceeding the recommended 5,000 items per folder, and would like to do this without having to access each user's mailbox individually. Thanks, Mark Pfdavadmin. Exchange: Exporting Mailbox Properties Using PfdavAdmin http://msexchangetips.blogspot.com/2006/08/exchange-exporting-mailbox-properties.html James Chong (MVP) On Mar 13, 6:04 pm, markj <m...@discussions.microsoft.com> wrote...

Counting values within a Date Range
I am trying to find a way to count the number of values for a specific user defined date range. HELP! Might help more if I am more specific. I have a column for order type (i.e, Floor, Roof, Wall) then a column with the date that order was created. I would like to be able to identify how many orders by "type" within a certain date range. Is that possible? This would be something that I want to generate every month so I would probably be using first day of month to last day of month. Any help would be GREATLY appreciate! "Jana" wrote: > I am trying to find a way...

order of days
Hi everyone, I have in A1:Ax dates, formatted as "mm.dd.yyy", i need a formula that will tell me which day is that day, but in the known way like monday, tuesday,.... i know how to do this, i want to get something like this: FIRST Monday of December 2008, like getting the date and telling me which monday of this month is it, or say second saturday, fourth wednesday, such things i mean. i know how to get the last part (Monday of December 2008) but i have no clue on how will i check which one is it? like first second, third? how can i do this? Thanks in advance for any help try this...

Word count and language settings #2
Is there any way you can change the language setting for a whole document e.g. many text boxes at the same time. Also, is there any way to count words in all text boxes in Publisher at the same time. I know you can edit to Word, but this is box by box, so takes for ever. ...

Number of days to birthday
This should be really simple but I hate working with dates and it's driving me crazy. I have a query that is supposed to have a calculated field that show the number of days to a person's birthday but I cannot get it to work correctly. Can anyone put me out of my misery on this one and post an example? Andy On Thu, 14 Jun 2007 09:58:57 +0100, "Andy" <Andrew.whitelaw@noJunqMailbaesystems.com> wrote: >This should be really simple but I hate working with dates and it's driving >me crazy. > >I have a query that is supposed to have a calculated fie...

Counting the number of "Y" in a row
Hi all I know this has to be pretty simple but I can't quite figure it out. I want a field at the end of each row which will count the number of fields with a "Y" and show the total number. I tried an IF statement but this went all wrong and I have tried the COUNT () statement but was not sure how to get this to count letters. Can somebody please help? Have a look in the help index for COUNTA -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Mikey C" <m.cringle@gmail.com> wrote in message news:1173871396.451963.103300@p15g2000hsd.googlegroups.com....

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...

How can I get the "PDF Complete" back on my menu bar?
I used to have a menu that said "PDF Complete". I noticed today that it is gone. Does anyone know how to get it back? May have been placed there by some Add-in. Tools>Add-ins. See if anything there could be the origin of the item. Look for something to do with "PDF" and checkmark it. Gord Dibben Excel MVP On Tue, 5 Oct 2004 13:45:35 -0700, "A Blaylock" <A Blaylock@discussions.microsoft.com> wrote: >I used to have a menu that said "PDF Complete". I noticed today that it is >gone. Does anyone know how to get it back? ...

If Count is not in a certain range, delete Rows
I have a spreadsheet that is 59752 x 2, with the one column Contact ID A and the other column Contact Link ID. It is like an instant messaging system. It looks something like this: 1 2 1 3 1 15 2 1 2 15 I want to count by different values in column A, and any count that is not within one of several ranges, those rows get deleted. So if the range is 3-5 contacts, then I want to keep the set of 1s, but delete the set of 2s. My ranges are 4-5 contacts, 30-41 contacts, and 100+ contacts. I am also brand new to macros, but I have experience with some other coding ...

Count rows based on multiple criteria
I have a need to count the number of rows in an Excel spreadsheet based on contents of multiple cells. The formula is on one worksheet and all data is on a second worksheet. IE: count all rows that have the word "Windows" in column B and the word "Complete" in column G. Have not been able to figure this out with common functions like count, countif, dcount, etc. Help appreciated. -- DMG IT Professional Search for SUMPRODUCT or SUMIFS if you use XL-2007 "dmg" wrote: > I have a need to count the number of rows in an Excel spreadshee...

Get count of append without appending?
I have several append queries. I would like to know how many records each would add to the target table without actually adding those records (the decision as to whether to add or not may be subjective). Is there a way other than duplicating the append queries with a select query? Dick Cleaveland hi Dick, news2@rgcle.com wrote: > Is there a way other than duplicating the append queries with a select > query? No, not really. Option Compare Database Option Explicit Public Sub CountAppending() On Local Error GoTo LocalError Dim db As DAO.Database Set db = CurrentDb B...

How do I count non blank rows only?
This seems so simple but I've spent hours without resolution. In column A, I need to count the number of rows that actually have data in them. If there is no data, I need to keep column A blank. Here's an oversimplied example of my simple list. 1 John Doe 2 Susan Smith 3 Joe Dear In column A now I have =COUNTA($B$2:B64) and that works great until you hit a row with no data. I have tried all these: =IF(G69>0,(=COUNTA($B$2:B69))," ") Also =COUNTIF($B$2:B69,">0") There's got to be an easy way that I'm missing!! HEEE...

Display the days between start & end date columns
I have two columns one labeled start date, the other Date Completed. I have a third column where I need to display how many days it took to complete the process from start to finish. Do I need a macro? I have no idea how to format the third column to calculate the days between the other two. Hi, Try this un-documented formula =DATEDIF(A1,B1,"d") For help on this formula look here http://www.cpearson.com/excel/datedif.aspx -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still suffic...

Help can't do a complete pc restore!
My hard drive crashed recently after upgrading a couple months ago from Vista to Windows 7. Prior to the hard drive crash, I completely backed up my pc to an external hard drive. As soon as I had a new hard drive in the pc, I restored everything. I was hoping it would restore the OS as well but I'm back with Vista, what I had before I did the restore. Is there any way for me to find out if my external harddrive does have the files to reboot Windows 7? I have a "windowsimagebackup" folder in my external harddrive that I can't view. Could this be the files needed to r...

Clear auto-complete
When I begin filling out my distribution lists, I get those (usually helpful) drop-down prompts which attempt to auto-complete my entries. However, our institution just rebuilt it's global directory, so most of these drop-down choices are obsolete. As I'm using the new addresses, they are adding to the list but everyone's becoming duplicated, and the old names are (unfortunately) alphabetizing above the newer entries. How can I clear these lists and start over? Chris ...

when re-running installer to install omitted program (Entourage) does complete re-install, then fails
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel Hi Everyone. I installed Word and PowerPoint a few weeks ago, and I want to now install Entourage. So I re-ran the installer ("/Volumes/Microsoft Office 2008/Office Installer.mpkg"). It didn't give me any options for selecting Entourage. Instead it just kicked off an installed *everything*, including Word and PowerPoint. Crap. This is very upsetting as I DON'T want messenger, excel, etc. Adding insult to injury, the program asked if I wanted to install the update ("Install Microsoft Office 2008 fo...

Count Function #4
Does anyone know of a function that will count how may unique items exist in a list? For example, suppose the following list: A B A C D D I would want the formula to return an answer of 4, since this is the number of unique items (A, B, C & D) and so not to count the multiples of some items. Any ideas? Many thanks in advance. Hi Matt! See: Chip Pearson: http://www.cpearson.com/excel/duplicat.htm#CountingUnique Various formulas for counting unique entries -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classificatio...

Online defragmentation is not completing successfully... Please he
Hi all, I have Exchange 2003 SP1 mailbox server: 3 Storage Group, 6 Databases... From 5 Databases I regularly get the message in event log: ESE 701 Online defragmentation has completed a full pass on database... (from 3 of them on nightly basis, from 2 database once a week on weekend)... From 1 Database (I've been getting the 701 message regularly as well), I don't get the 701 message any more (3.5 months now)... It makes me worry... What would you suggest me to do? Any ideas would be welcome... Thanks! Victor Do you get a 700 indicating that the online defrag has ever started? Nu...

delete old email addresses from auto complete
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Email Client: pop When I type in an email address into the to line, Entourage automatically fills in, which is convenient--except when it's with an old outdated email address. <br><br>Even when I go to my address book and delete an old email address and replace it with a new one, the old one stays stuck in the autocomplete feature. <br><br>How do I get rid of old email addresses that are lodged in the auto complete memory? wavewriter@officeformac.com wrote: > How do I get rid of old email addres...

First day of Next Month
Hello, In A1 I have the date 3/10/10. In B1 I would like to insert a formula that will show the Date 4/1/10. Basically, I would like a formula that will show the first of the next month no matter what date is shown in A1. Thanks for the help. Try this: =DATE(YEAR(A1),MONTH(A1)+1,1) -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== "Jim" <Jim@discussions.microsoft.com> wrote in message news:3A3346E7-C4DE-...

count nums syntax
i would like to count the number of times the number1 appears in column L. what is the proper syntax for this formula? TIA look in HELP index for COUNTIF "Larry" <fromMy@website.com> wrote in message news:l8ehlvokq1fo01bp7m820ga02jdfsffotl@4ax.com... > i would like to count the number of times the number1 appears in > column L. > what is the proper syntax for this formula? > TIA Larry, You can use the COUNTIF function to do this. E.g., =COUNTIF(L1:L100,1) -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com chip@cpearson.com "Larry&q...

Counting within a group
Hi, In my Summary report, there are two groups: jobNo within Category. jobNo contains data which can be repetative, but not for all records. In other words, there can be: 6 records with jobNo 1234, 2 records with 4567 and 1 record for 9701. I want to count the number of times that jobNo changes, but not the total number of jobNo records. I tried a query using an expression Max(jobNo), then tried to sum it in the report, but it gives the total number for jobNo's. How can I achieve the result I am seeking? Thanks in advance, Jake If you want to list all the records on the repo...

attachements removed after 30 days in exchange
Hi can anyone help, I am looking after a SBS2003 server and I am trying to find out if its possible to remove attachement from emails after 30 days i.e automatically delete all attachements to emails tha are older than 30 days. Is it possible ? if so can someone point me in the right direction Not natively. You'd need to look at some sort of true archiving solution for this (think something like KVS). Exchange can remove messages (including any attachments) via Mailbox Manager policies, but you can't get down to the attachment level. -- Ben Winzenz Exchange MVP "durk&qu...

how many months and days between two dates?
Friends, If I have two dates: February 5,1999 and January 4, 2002, how can I calculate the number of months and days between both dates? Thank you for your time. shopgirl <shopgirl@discussions.microsoft.com> wrote > Friends, > If I have two dates: February 5,1999 and January 4, 2002, how can I > calculate the number of months and days between both dates? > Thank you for your time. With the first date in A1 and the second in B1 Try =DATEDIF(A1,B1,"m")& " months "&DATEDIF(A1,B1,"md")&" Days" For more information on the und...

outlook freezes when viewing in day,week, month mode
Hi, WHen I navigate to the calendar function Outlook 'locks up' and does not respond. Waiting for the activity to proccess does not work and i have to close it down and re-open. However, if I change to view to the list mode this doesn't occur. This seems to have started happening only after loading Business Contact Manager. I have reloaded both but still have the same problem. Can anyone help. On 3/8/2010 10:19 PM, GT Query wrote: > Hi, WHen I navigate to the calendar function Outlook 'locks up' and does not > respond. Waiting for the activity to p...