countif and dates

My spreadsheet includes a column with the date (mm/dd/yr) of the
entry. Can I use countif to count the number of entries that were made
in each month? Thanks, JAF
12/3/2004 3:16:03 AM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies

Similar Articles

[PageSpeed] 47


Try this:


Replace the 1 with the appropriate month number.


>-----Original Message-----
>My spreadsheet includes a column with the date (mm/dd/yr) 
of the
>entry. Can I use countif to count the number of entries 
that were made
>in each month? Thanks, JAF
biffinpitt (3172)
12/3/2004 6:01:15 AM
Just a heads up in case there might be blank cells, the formula will count 
blank cells as January


will not


Peo Sjoblom

"Biff" wrote:

> Hi!
> Try this:
> =SUMPRODUCT(--(MONTH(F1:F10)=1))
> Replace the 1 with the appropriate month number.
> Biff
> >-----Original Message-----
> >My spreadsheet includes a column with the date (mm/dd/yr) 
> of the
> >entry. Can I use countif to count the number of entries 
> that were made
> >in each month? Thanks, JAF
> >.
> >
PeoSjoblom (789)
12/3/2004 4:45:01 PM

Similar Artilces:

dates appear as error hashes
When I type dates in cells on an Excel spreadsheet they become a series of hashes. But other dates of the same format in other workbooks are fine. Why does this error keep on occuring. I think may be the cells are not in the right format for me to add dates. Can i change this?? Hi Try widening the columns. Does this resolve the problem? Regards Roger Govier pembers wrote: > When I type dates in cells on an Excel spreadsheet they become a series of > hashes. But other dates of the same format in other workbooks are fine. Why > does this error keep on occuring. I think may be...

Help with date ranges
Hi all, I've got a sheet where I have some columns with dates in and I want a column with the number of working days between them. I've used the DAYS360 function, which gives a rough indication of the number of days, but it's not very accurate. Can anyone give me an idea of now to do it, a script, a plug in, or a different app I can download to do it? Help really appreciated. -- MattG Look at NETWORKDAYS and WORKDAY in help, they are part of office/excel but need to be installed (Analysis ToolPak) -- Regards, Peo Sjoblom "MattG" ...

how to get rid of default dates in Excel?
I want my dates to stay as I have typed them - not for them to be changed for the Excel default system. Then you must format the cells to your date entry style. Either before or after. BTW............Excel does not control default date format. That is controlled by your Windows Regional and Language settings. Gord Dibben MS Excel MVP On Mon, 22 Mar 2010 07:51:01 -0700, Margaret L. B. <Margaret L.> wrote: >I want my dates to stay as I have typed them - not for them to be changed >for the Excel default system. 2 optio...

Compound condition with COUNTIF
I want to be able to count values between 2 dates in a table. The query will look something like: =COUNTIF('Roll Out Schedule 23-Nov-05'!$H$3:$H$953,AND(">27/11/05","<05/12/05")) Can anyone tell me where I'm going wrong? =COUNTIF('Roll Out Schedule 23-Nov-05'!$H$3:$H$953,">27/11/05- COUNTIF('Roll Out Schedule 23-Nov-05'!$H$3:$H$953,">05/12/05") -- HTH RP (remove nothere from the email address if mailing direct) "Mike McLellan" <> wrote in message news...

Date Question
In Money 2006, In previous versions of Money you could set the check register to show either the date an EPay was processed by the back or the date it was due at the Vendor. In Money 2006 I can't seem to find that setting. Can anyone direct me to the right place? Thanks. Steve ...

Using Date as a Variable
I have cell contents and formulae in cells with fixed dates in them. I need to create other versions of the workbook with different dates. So I created a date variable. But when I run the procedure to replace the fixed dates with another date, I get an error message: Run-time error '1004': Application-defined or object-defined error Here is a simple example of what I have in the code. Dim sCripDate As String sCripDate =3D "2007,10,10" With Worksheets("Cost") .Range("D8").Value =3D "=3DDATE(sCripDate)" End With I must be...

How to Change Date Formats
I have a bunch of raw data that I am working with. One of the columns displays dates and I am trying to strip out the year. If I try to use the YEAR function I get strange answers back. For example: Cell reads: 1/1/2006 Answer using YEAR function: 06/1905 Answer that I want: 2006 My data comes from a CSV file, so I think maybe it is in a different date format than excel uses as a standard. Any suggestions on how to reformat this data or some other formula I can use to get the answer I am looking for? If the cell containing the date is not recognized as a date format then you might ...

count records based on date range
I have a list of records about which I'm trying to summarize differen information. One of the columns in the spreadsheet is a created date. What I want to do is create three summary fields, one that shows a coun of the records from today to six days ago, a second field that shows count of all records from seven to 14 days ago and the last fiel showing a count of all records over 15 days ago. This sheet will b updated regularly, so I don't want to have specific dates in m formulas. I used the COUNTIF function to do summarization for different part of the spreadsheet, but I can'...

dates and ages
I have a list of names and birthdates in the format: Diane 5-2-00 Joey 2-9-01 Nancy 12-16-99 I would like to add a third column that calculates the ages of the persons listed based on todays date (in days). Does anyone have any idea how to do this? Thanks! mp =TODAY()-B1 format as a number -- Message posted from mp Check out The DATEDIF Function. Info at Chip Pearson's site. Gord Dibben Excel MVP On Tue, 22 Jun 2004 07:21:31 -0700, "mp" <anonymous@discussions.m...

Newbie here
Hi all. I'm new to the group and do very little with excel. I'd like to do the following just to to make my life easier at work. I know about conditional formatting, but what I want to do is a bit too complex for that, I think. Therefore I must need VBA but I've never used it before. Any pointers please? I am doing a sheet which will keep track of the training needs of a group of people. Some training needs to be done once every 6 months, some once a year and some just once every 2 years. So I have names on the left in the rows and each column refers to an item of training....

Entering date in a cell so that it falls in specific way when sorted?
I have an entry that I know falls in February 2004 but that we don't have an exact date for as that information has been lost (ahem, _not_ may I say by me [thank goodness!] <lol>). Anyway, all the other entries for this date column are in the usu. m/d/yyyy format even though they have a custom setting that has it display differently. My problem is one I've run into many times before - how to have a, say, 2004.02 entry (for Feb.2004) sort between the last January 2004 one and the first February 2004 one? Is it possible to enter the Feb.2004 date in such a way that it will sort ...

Excel 97/2000 - Autofilter
Hi, Im trying to find my perfect date using Excel (haha). I have all my friends in a database, with Autofilter on. Since I'm male, I first filter on the column sex=f, than second filter is on the column married=n. This works ok, all my non-married female friends are showing. Next I filter on salary=top 3 and the result is empty! I know that my non-married, female friends are not in the salary top-3 of my database, but that is not what I'm looking for. Im looking for the top-3 salary unmarried girls! What do I do wrong, this way I always stay single ! Regards, Ronald Ps. Yes I know, ...

Date Calculations #3
Cell A1 contains accounting Period start Date say 1/2/04 A2 contains accounting period end date say 31/1/5. A3 contains a different start date say 1/4/04 and cell a4 contains different end date say 31/3/05. I need a formula to tell me how many of the days in 1/4/04-31/3/05 fal within the accounting period. Thanks in advance for your hel -- Message posted from try using the datevalue(date) function, it returns a number value and i the two are subtracted, it should work out to the number of days. so if you take the time frame and find out how many days are in it...

Cell colour according to date
Hi, Is there a formula that would change the cell colour if a date in a cell was out of date afetr a set number of years. Example: Cell B9 contained date 140506 I then want the cell B9 to turn red when the date reaches 2 years later 140508. Thanks in advance Jason Let's assume that your example date is 14 May 2006. Click on B9 and: Format > Conditional Formatting... > Formula Is > =TODAY()-B9>731 then pick the colour red. -- Gary''s Student - gsnu200773 Use conditional formatting with a formula of =DATE(YEAR(B9)+2,MONTH(B9),DAY(B9))<TODAY() -- --- HTH Bob ...

Inventory Dates #2
If I am to add all the shipments for all different equipment types by receipient and other criteria, eg =SUMIFS(RECORDS!TOTALS,RECORDS!MONTH,$A$2,RECORDS!EQUIPMENTTYPE,C$2,RECORDS!RECIPIENT,$A4) This formula is validating the receipient, the equipment type and even the month it was shipped. Adding totals of all shipment by item description. Our inventory starts on the 22nd of each month and ends on the 21st of the following month. Up to this point, I had to add a column to specify what month's inventory the shipment must be applicable to, otherwise is considering the month to start ...

Date Ranges / Report / Displaying Data
Hi, I have a report named “rptLocal”. This report is made up of approx. 50 queries. How would I go about making it so that the report only displays data ranging between two dates? I would like make it so that the user would enter the date ranges into textboxes within a form “frmReport”, presses a command button and poof the report. Is this possible? And how? Thanks -- Message posted via 50 queries in a report? Wow, that's a lot of different information. If you want each query to be filtered by ...

Help with Conditional formatting with Dates
Hi there, I have a spreadsheet in which I have to monitor various dates which are in different columns (i.e. (1) Date Tender Released, (2) Date Company Selected, (3) Date Draft Contract Forwarded, (4) Date Contract Signed etc...). Would if be possible to Conditional Format and have diffirent ROWs highlighted for each individual Contracts (I have 300 running contracts running) every time a date is filled in these different columns: Initial Step (1) = Yellow (Call for Tender released) Step (2) = Blue (Company Selected) Step (3) = Brown (Contract under Process) Step (4) = Green (Contract Si...

how to include real time & date on a worksheet
how to include real time & date on excel spread sheet I was supplied with a small macro that would enter the current date/time in a cell when invoked. Sub NOWTIME() ActiveCell.Value = Format(Now(), "mm-dd-yyyy h:mm:ss AM/PM") End Sub Is that what you want? Barb Reinhardt "nde" wrote: > how to include real time & date on excel spread sheet ...

running total between two dates
I am trying to have my spreadsheet sum up purchases that fall between (month) 12th to (month) 12th, and when/if a new purchasing month starts, restart summing the purchases. Column G are purchase amts. Column A are purchase dates. Originally, I just referenced month to month, with Column I formula: =MONTH(A5) etc., and my running total forumla: =IF(I4<I5,G5,IF(I5=I4,(G5+J4),0)) but because my purchasing dates are middle of the month, this is not working so well. I would like something I can fill down and not have to rewrite for each date. I've looked through the...

Turn off auto date recognition
Hi All, Is there a way to keep Excel from turning text that might represent a date into a date? For instance a user is entering text such as june20, march08. If we format the cells as text before entering it is fine until he tries to use the Replace feature. During the Replace process Excel overrides the text formatting. Is there anyway to turn this off? We forced it with an apostrophe, but that seems like a sloppy fix. Any suggustions would be appreciated. Thanks. Erin ...

Excel ignores boot-time regional settings when interpreting a date
I have a third party DDE app that exports dates as strings, correctly using the short date format in the regional and language settings, which, in my case is MM/DD/YYYY (reset at every boot). Unfortunately excel insists in interpreting that date as DD/MM/YYYY [Application.International(xlDateOrder)=1, it should be 0], disregarding my regional settings. The result is that all the dates are wrong or #VALUES! If this is not wierd enough, listen to this: it only happens after I reboot (when the date format is reset to MM/DD/YYYY). If subsequently I change the short date format in the regional se...

Date some qualifies
Ok, I need help with a formula (if you have time). If I have someone’s hire date and birth date, I need to calculate what date they will qualify for retirement, either equals age 60 plus years of service = 75 or age 65 (whichever is earlier). Thanks!! -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions (Remove ^^ from email) "sgwill" <> wrote in message > Ok, I need help with a formul...

Calculated Field in Query
I have the calculated field below in a query. I was expecting to get the results in the query (for this field) formatted as a date. But this is not happening. Can anyone explain why, and suggest solutions? Thanks in advance, Paulo Relevant_Date: IIf([Payment Terms]![Counting From]="Shipment",IIf(IsDate([Orders]![Ex-Factory2]),CDate(DateAdd("d",[Payment Terms]![Payment Days],[Orders]![Ex-Factory2])),""),IIf([Payment Terms]![Counting From]="Invoice",IIf(IsDate([Orders]![DC Received Date]),CDate(DateAdd("d",[Payment Terms]![...

Charting only the specified dates
Hi all, I am using Excel 2002 SP-2 on Win2k Prof. Q1. I have the following data for testing Date Val 20/09/04 2 21/09/04 4 22/09/04 8 23/09/04 16 24/09/04 32 27/09/04 16 28/09/04 8 29/09/04 4 30/09/04 2 And, when I simply chart it the chart shows missing dates on X-Axis, 25/9/04 and 26/9/04, with interpolated values each, according to either end of the closest values (24/9/04 and 27/9/04). Why does this happen? How can I turn this on/off? Q2. In relation to Q1, I try to create PivotChart and this time need interpolated values for dates not specified in PivotTable. How can I do this? ...

Calculation of days between 2 dates
Hello everyone, I have a problem: I want to calculate then amount of days between two given dates. A1: 01.02.2006 A2: 01.03.2006 A3: amount of days How can i do that? Thanks for any help! Greetz Jochen =A2-A1, Format as General, not as Date, which Excel does automatically -- Kind regards, Niek Otten "Jochen Munzinger" <> wrote in message news:e27fmm$257$ | Hello everyone, | | I have a problem: I want to calculate then amount of days between two given | dates. | | A1: 01.02.2006 | A2: 01.03.2006 | A3:...