How do I count number of days from a range of dates & some repite.

I am using excel 2007 and I need to get the count of days from a range of 
dates where some dates are repited; one diferent date is equal to one day.
I will aprreciate any input    
0
Utf
12/2/2009 3:51:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
1050 Views

Similar Articles

[PageSpeed] 41

Hi Juan

If you mean to get the distinct count of dates then try the below with dates 
in the range A2:A10...Change the range to suit..

=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&""))

-- 
Jacob


"Juan Roubaud" wrote:

> I am using excel 2007 and I need to get the count of days from a range of 
> dates where some dates are repited; one diferent date is equal to one day.
> I will aprreciate any input    
0
Utf
12/2/2009 4:10:01 PM
You can just subtract one date from another to get the number of days
difference (format the cell as general or as number with 0 dp).

I'm not sure exactly what you are asking, but perhaps this:

=3DMAX(D:D) - MIN(D:D)

assuming your dates are in column D.

Hope this helps.

Pete

On Dec 2, 3:51=A0pm, Juan Roubaud <Juan
Roub...@discussions.microsoft.com> wrote:
> I am using excel 2007 and I need to get the count of days from a range of
> dates where some dates are repited; one diferent date is equal to one day=
..
> I will aprreciate any input =A0 =A0

0
Pete_UK
12/2/2009 4:13:11 PM
Reply:

Similar Artilces:

How to generate auto increment invoice number
Is it possible to generate auto increment invoice number where we already have existing records. If it is possible, how? Also, this database will be hosted on a website and accessed by 2 or more users - at times simultaneously. Please explain step-by-step as I am very new to Access. Thank you, thank you, thank you in advance. You can look at the existing table, find the highest value using the DMax() function, then add 1 to it and immediately save the record so that you are less likely to conflict with another user. The code for a new record is simple (aircode): Sub Form_Current() ...

Group By Flag Status and then Received date
I want to group first by flag status and then by Received date, but have the same collapsable headers available in the Received that you get if you group first by Received date. It looks like it may not be possible, but thought I would post to see. Thanks! Following is the example. So: Group: Flag Color Unflagged (Then emails by date with this flag color grouped as the following) Date: Today Date: Yesterday Etc. Instead of Group: Flag Color Unflagged (Where the groups by date are specific including the time). Date: 6/9/2006 4:18 PM Date: 6/9/2006 4:12 PM ...

how do i ignore a number when using MODE function?
Hi I have a list of numbers that I want to find the MODE in: 0 0 1 2 0 0 3 3 2 2 2 0 0 But I want to be able to ignore the Zeros How do i go about doing this so the mode function gives me the #2 only Thank you -- Dev4me ------------------------------------------------------------------------ Dev4me's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29709 View this thread: http://www.excelforum.com/showthread.php?threadid=498091 =MODE(IF(A1:A20<>0,A1:A20)) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the...

formula to retrieve the count of a certain value over a range of cells
Need to do some simple stats, and thought it should be easier. Looking for a function to give me a count of all the cells that equal certain value in a certain range. ie.. Give me a count of the number of cells that equal the number 1 i the range a1:a25. Help -- trev ----------------------------------------------------------------------- trevc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1683 View this thread: http://www.excelforum.com/showthread.php?threadid=32022 =countif(a1:a25,1 -- Gromi -----------------------------------------------------------...

date and time of outlook pop a/c of exchange
dear respected gurus, i would like to know: - v have one staff - he changed the time in windows 98 machine and send email to other staff and blames to IT department that emails r delayed - v used outlook xp client configured pop with exchange 2k can any one tell me how to use server email time or any other software/plugin to stop changeing timing from system of win 98' rgds shahzad MUHAMAMD SALIM SHAHZAD wrote: > dear respected gurus, > > i would like to know: > > - v have one staff > - he changed the time in windows 98 machine and send email to other > staff and ...

Count if equal to first non error cell
Hi I tried to post this once so apologies if this is second post... I want to count the number of times 3 or -3 occurs in range A8:A15 depending on whether 3 or -3 comes first. If 3 is A8 then count how many times 3 occurs in the range. If A8 and A9 equal #n/a and A10 equals -3 then count how many times -3 is in the range (there many be 2 -3's and one occurance of a postive 3 for example) Thanks ...

cancel date formatting
How do I cancel the feature that automatically converts number to date. I'm using excel and want to enter the value 1-1-0 (1 loss, 1 win, 0 tie). Excel interprets this as 01/01/2000. when I try to format the cell to text 1-1-0 is interpreted as 36526. Please help. Doug Either format the cell as text first, or enter the information with an apostrophe before it (eg '1-1-0) Andy "Doug" <douglasherb@hotmail.com> wrote in message news:014101c38ced$d4f13060$a301280a@phx.gbl... > How do I cancel the feature that automatically converts > number to date. I'm...

Exchange 2003 Mail between servers stops after a few days
Hi all, I have an odd situation where mail destined for a different administrative group in the same domain stops after a few days; a restart of the server where mails are queing clears the problem. The destination admin group is attached via a connector (smtp), and it's this connector that goes into a retry state under fault conditions. A bridgehead at either end is used, although looking at message tracking suggests the bridge-heads are not being used. Local mail delivery is unaffected, only mail to the remote admin group is affected. There are firewalls involved: the sites are...

Case Numbers Jumped
Has anyone experienced this issue? In CRM 3.0 all of a sudden my case numbers jumped up. Example: CAS-07624-OT4Z13 to CAS-08164-4E828E These cases were created by a user in our Support Dept. who notified me of the discrepancy and I ran a query to search on cases between these 2 parameters with a result of 0. What table do I look in and should I modify it? ...

Maximum number of process....Windows
Hi, Is there any limit of number of process or thread that can run at a time in Win9X and win2k/XP OS. Plz reply. regds vinay Well, on the toy systems (Win9x), the process limit is fairly small, probably reasonably on the order of a few hundred, but I've never measured it. The limit is based on the available real memory, the available paging space, the amount of GDI space, the number of windows, etc. (the USER and GDI space is shared system-wide under MS-DOS), and, or course, the sizes of the processes. On real operating systems, you will probably run out of patience before you r...

group of numbers
How can I make a bunch of numbers being recognized as a group? I've got the following numbers: 1, 2, 3, 4, 5 and 6. I want them to be considered as a group. How can I achieve it? Thank you for your attention --- Message posted from http://www.ExcelForum.com/ You can name the range of cells that contain the numbers. There are instructions here: http://www.contextures.com/xlNames01.html If that's not what you're trying to do, perhaps you could give a bit more detail. PeaceMaker < wrote: > How can I make a bunch of numbers being recognized as a group? > > I&...

Summing and Counting based on 3 criteria
I am trying to sum and count based on 3 criteria from another tab with no luck. Example I want the sum of the criteria in column P in sheet2 if column B="Phoenix", column E="1", and column j="807" and return that to a cell in sheet1. Same scenerio with the counting. Any help would be very appreciated. Thanks Todd Hi try =SUMPRODUCT(--('sheet2'$B$1:$B$100="Phoenix"),--('sheet2'$E$1:$E$100=1) ,--('sheet2'$J$1:$j$100=807),'sheet2'$P$1:$P$100) if your numeric values are stored as real numeric values. -- Regards Fra...

Automatically send email based on date value
Is there way to send an email automatically based on a date field in a table? Some more information about what I am trying to do. I have a form where users enter in upcoming bids. I would like to send a reminder email out some specified time (ie, 14 days, 7 days, and again 3 days) prior to the bid being due based on the Bid due date field in the table. I don't want to have to go into the form and hit a button, I would just want it to review the table perhaps every time the database is open or even when the form is opened and send the email. The email only needs to go to one person, so ...

Percent to Whole Number
I have an Excel table saved as *.csv I want to turn decimal numbers into whole numbers without the decimal (or - the same - remove the percent sign) from many numbers in a table. This should be simple but seems not to be. With a two digit number, e.g. 0.25, one can search for the decimal and replace it with a blank and get the desire result (in number format and using value rather than formula). However a number such as 0.3 becomes 3. If 0.3 is converted to 0.30 (formatting for two decimal points) and a search is made for the decimal, replacing it with a blank, the result is 3.00. I want ...

Calendar dates in all users outlook
G'Day All, I am running E2K and all users are running Outlook XP on their desktops. Without visiting all users computers how can I update all users' Outlook Calendar to show the days/dates the office will be closed? Thanks I have the same problem. The only workaround I could find was to send out an email meeting invitation to everyone and instruct them to click accept (which they eagerly do if it's a holiday). If you find a better way let me know! Chris ...

Item's Last Sale Date
Hi there I'm creating a smartlist report that will display the item details but not sure which inventory table I should use to get the Last Sale date of an item. Any ideas? thanks. You can try this query to fetch the item number with the highest date of sale (or last sale date): SELECT ITEMNMBR, DOCDATE FROM IV10201 GROUP BY DOCDATE, ITEMNMBR HAVING DOCDATE =3D MAX(DOCDATE) AND ITEMNMBR =3D 'xxx' You can change 'xxx' with your item number. I've not tested this fully so you may want to run this in your environment and make sure that this query will work for every ...

Counting Months
Guys, I need to do something clever with months - although right now I'm not quite sure what the best approach is. I'm developing a yearly Company Ledger workbook with 13 sheets, one sheet per month, plus one 'Setup' sheet as the first sheet. The idea is that the 'Setup' sheet is filled in with all the company details, and this information is used to populate the appropriate cells in the subsequent twelve monthly ledger sheets. I'd like the monthly ledger sheets to calculate the (UK)VAT due to HM Revenue & Customs (I don't have a problem with the cal...

Counting strings of consecutive numbers in a column
Hi I want to be able to count strings of consecutive numbers in the one column. eg: Seagrass Seagrass Seagrass Seagrass NA NA NA Seagrass Seagrass Seagrass NA ... So i want to count the number of times Seagrass occurs in the one string, then count how many times it occurs int he next string. eg in the example above: 4 & 3. I am at a total loss thanks Check out your previous post.. -- Jacob "SamG" wrote: > Hi I want to be able to count strings of consecutive numbers in the one column. > > eg: > > Seagrass > S...

Specific Age Query for a Date Range
Table = Personal Field = Birthdate I need to create a query to show who is of a certain age for January 1, 2008 to December 31, 2008. For example, I need to know who will be 50, 60, 65, 70 and 75. I would like to create the query so that when I click the query to open it, I have to input the age. I've done other queries to show who is of a specific age as of today: AgeYears: DateDiff("yyyy",[Birthdate],Date())-IFF(Format([Birthdate],"mmdd")>Format(Date(),"mmdd",1,0), with criteria of [Age in Years] ... but I cannot figure out how to pull a report...

Dates pre 01/01/1900
Hi, I can manipulate dates and times fine when they are post 01/01/1900, and I know that this is the base date for Excel date numbering. I had automatically assumed, but never needed to apply, that when I wanted to calculate historical dates prior to 01/01/1900 that the values returned would be a minus value, but still a valid number. I now want to calculate values using dates prior to 01/01/1900 and find that even if I format the cells to date and then enter a date, the values are assigned as text, and therefore I am not able to do any calculations. For example I cannot calculate the...

You a 7-Day FREE Trial
We Are Offering You a 7-Day FREE Trial to the internet's Hottest New Business Opportunity! If you missed out on the DOT COM boom, now is your chance to cash in on the massive and growing global demand in our $20 BILLION PER YEAR market. We'll show you how to create an income that will come to you for years and grow with each passing month. A SIMPLE, Fully-Automated, REJECTION-FREE Internet Marketing System! Finally, an EASY, proven, FREE system that will truly help anyone willing to follow our guidance succeed! Average People Are Making Money! Average people are putting their home...

Count Based on Comparison with Two Fields
I recently learned that I can count the number of items in one column that match items in another column by using: =COUNT(MATCH( 'Sheet1'!G2:G15001, 'Sheet2'!A2:A15, 0)) However, I now need to count records on one sheet in which TWO fields match items in both of two separate columns (i.e., a comparison of two columns to two columns and a count of records in the first that have fields matching elements in the second) . My attempt to use the above statement with an AND failed. Can someone explain how to do this? Hi I would suggest that you post a before a...

Spin Box with Dates and Lookup
Hi, I'm trying to design a spin box function which pulls in data from monthly tabs, the month being determined by a spin box. I have monthly tabs Jan-10 to Dec-10 all containing an identical table with different data in. The user needs to be able to change the month of lookup using a spin box function but I cant get it to work properly. Thanks in anticipation. Hi, You are not very clear about your question. Anyways, try this Right click on the spin control box which you have drawn ad give the lower and upper limits are 1 and 12 respectively. In the cell link box, ...

Formatting dates prior to 1900
I need to enter dates in dd/mm/yyyy format prior to 1900 like 14/02/1834. Can this be done? Thanks -- Pete "Pete the Mag" <PetetheMag@discussions.microsoft.com> wrote in message news:307B8190-C642-406F-A6DE-5F3BFBC78CEB@microsoft.com... > I need to enter dates in dd/mm/yyyy format prior to 1900 like 14/02/1834. > Can this be done? > > Thanks > -- > Pete Use an apostrophe as the first character, i.e. '14/02/1834 the postrophe won't appear and Excel will treat what follows as text. /Fredrik Thanks but, I should have correctly posed the questio...

Count if formula with two variables
Hello, Am trying to figure out a formula in a cell to analyse data in tw columns. One column will have a referance and the frequency of th corresspomnding data in the other column need to be counted -- shrikan ----------------------------------------------------------------------- shrikant's Profile: http://www.officehelp.in/member.php?userid=474 View this thread: http://www.officehelp.in/showthread.php?t=123405 Posted from - http://www.officehelp.i attach an example. "shrikant" wrote: > > Hello, > > Am trying to figure out a formula in a cell to analyse dat...