Counting Dates

I have two spreadsheets, one containg data the other the results.  In my
results spreadsheet, I want to come up with the total number of
transactions processed for a specific month.  The formula I am using is
=COUNTIF('2005'!$E$2:$E$10000, A5).  A5 being Jan 1, 2005.  However, the
formula only seems to be looking for transations processed on Jan 1,
2005 and not for the entire month of Jan.  Is there a formula I can use
to solve this issue??  Thanks!


-- 
ROSE2102
------------------------------------------------------------------------
ROSE2102's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9202
View this thread: http://www.excelforum.com/showthread.php?threadid=494125

0
12/16/2005 2:28:20 PM
excel 39879 articles. 2 followers. Follow

2 Replies
471 Views

Similar Articles

[PageSpeed] 29

ROSE2102 wrote:
> I have two spreadsheets, one containg data the other the results.  In my
> results spreadsheet, I want to come up with the total number of
> transactions processed for a specific month.  The formula I am using is
> =COUNTIF('2005'!$E$2:$E$10000, A5).  A5 being Jan 1, 2005.  However, the
> formula only seems to be looking for transations processed on Jan 1,
> 2005 and not for the entire month of Jan.  Is there a formula I can use
> to solve this issue??  Thanks!

Yes, you need to enter this an an array formula.  To get more of an
understanding on what that is, look in the help for excel.  Here's a
formula you can adapt

=SUM(IF(MONTH(A1:A8)=9,1,0))

When you enter the formula, you need to hold down CTRL+SHIFT+ENTER.
This makes it an array formula

a1:a8 is your range, =9 is your month (in this example, september)

So what this does it checks the month of each cell from a1.a8.  If it
=9, it returns a "1" which is then summed.

There's probably other variations that you can use, but this is easy.

0
12/16/2005 2:55:53 PM
=SUMPRODUCT(--(TEXT('2005'!$E2:$E$10000,"YYYYMM")="200501"))

If you ever decide to just check for month, be aware that empty cells will
return 1:

with a1 empty:
=month(a1)
will return 1.



ROSE2102 wrote:
> 
> I have two spreadsheets, one containg data the other the results.  In my
> results spreadsheet, I want to come up with the total number of
> transactions processed for a specific month.  The formula I am using is
> =COUNTIF('2005'!$E$2:$E$10000, A5).  A5 being Jan 1, 2005.  However, the
> formula only seems to be looking for transations processed on Jan 1,
> 2005 and not for the entire month of Jan.  Is there a formula I can use
> to solve this issue??  Thanks!
> 
> --
> ROSE2102
> ------------------------------------------------------------------------
> ROSE2102's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9202
> View this thread: http://www.excelforum.com/showthread.php?threadid=494125

-- 

Dave Peterson
0
petersod (12005)
12/16/2005 3:11:57 PM
Reply:

Similar Artilces:

Can I import important dates (birthdays, etc.) into a calendar?
Can I import important dates (birthdays, etc.) into a calendar? I would like to be able to produce a family calendar with Office that will allow me to add dates such as birthdays, year of birth or death, anniversaries, and so on to a spreadsheet or Access database and then easily import those event dates into a Publisher calendar. Sure you can, the help files are your best friend. Articles abound, Mail and catalog merge http://office.microsoft.com/en-us/assistance/CH062524751033.aspx -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft....

Add current date to file name when exporting to Excel
Is there a way to programatically add the current date to a file name when exporting a query, table, report via a macro? -- LMR ...

Need help with date-time
Hi all, I have data that varies on a daily basis. There are three series of data - sales, productivity and refunds. For every series there's multiple data for each day. For example, on Monday 7th June there are three sales figures 10, 20, 30; three productivity figures 10%, 20% and 30% and three refunds worth $5, $10, $15. The data is entered on a daily basis. I want to create a chart that shows this detail on a daily basis i.e. on the X-Axis it should list date and for each date it should plot the values for those series. I have already created a chart with the three series however I ha...

How can I update my charts x axis (dates) automatically with new d
I have 2 questions and really need your help 1.I have data sheet and chart sheet.I am not using pivot table.For each new day I am adding one line for current day data to data sheet.I have around 60 charts.How can I get charts updated automatically.I am currently selecting each chart and extend data for adding one more line everyday to those 60 plots.(right click -selecting data and one more row from table) and it takes a lot of time. 2.Is there anyway to embed bubble (comment) data to specific date in axis.I have comments in bubbles for each day but they are shifting when I p...

Counting Dates #2 #2
What formula to read a range of cells with a lot of dates in it and than count how many times the current month shows up in it (has to recognize the year too because if it only counts all dates with November in them it cant count 2003, 2004 etc.. only the current month) Example: 11/4/2005 11/9/2003 11/12/2005 12/1/2005 This would need to give me a total of: 2 Thank you, -- na Hi! Try one of these: =SUMPRODUCT(--(TEXT(A1:A4,"mmmyyyy")="Nov2005")) OR: B1 = 'Nov2005 =SUMPRODUCT(--(TEXT(A1:A4,"mmmyyyy")=B1)) OR: =SUMPRODUCT(--(MONTH(A1:A4)=MONTH(TO...

A UDF for Counting Coloured Cells that are conditionally formatted
Hi All, I have been doing some extensive research and testing to determine whether there is any way that a UDF can be created that will return the count for cells on an input sheet that show a particular colour (as determined by CF). So far it would appear that this is not possible. I have accessed some of the most eminent MVPs and VBA gurus, but the conclusion would appear to be (and borne out by my testing) "No - can't be done" So this is my last attempt to find a solution - if it can't be done I can laboriously go throgh my 50 odd input sheets and mirror the CF con...

Balancing account and Last Statement Date
In Money 2004, whenever I balance an account, the next time I go to balance my account, the dialog box already has a "Last Statement Date" entered but always a month later than my last reconciliation. As I balance more frequently than that, how can I adjust the automatic insertion of dates? (I understand you can just type the new date, but I have not found a tool to make the one month automatic insertion either disappear or adjustable to another cycle). Thanks. All accounts shall be balanced one month apart, sayeth Money. Hand edit the date. There is no other way. <ano...

Count groups rather than detailed records
I generated a report that shows PartNo, Date of Last Shipment, Qty of Last Shipment. Because there may be more than one shipment of each PartNo, I grouped the PartNo and summed the Qty so my output looks like these: Part Date Qty A 1/1/07 10 B 2/2/07 20 If there were actually 2 shipments of A on 1/1/07 of 5 each, there would be 3 detailed records of the output. How can I get a count of just the unique PartNo...in this case 2? Thanks, -- TIA You may be able to solve the problem merely by changine the query into a Totals query. Depress the Total button on the toolbar ...

countif won't count
I'm trying to use the following function: Sub Find_Bold_Cat() Dim lCount As Long Dim rFoundCell As Range Set rFoundCell = Range("A1") For lCount = 1 To WorksheetFunction.CountIf(Columns(1), "Cat") Set rFoundCell = Columns(1).Find(What:="Cat", After:=rFoundCell, _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) With rFoundCell .ClearComments .AddComment Text:="Cat lives here" ...

numbers to dates #2
When I paste a set of numbers into EXCEL using the "Export to Microsoft Excel" function from IE, the numbers are changed to dates. Their original format is: 4-8 8-18 etc. They get switched to: 8-Apr 18-Aug Does anyone know how to turn this autoformatting off? I've tried formatting the cells back to numbers, text and general but then the numbers get changed to different numbers. 4-18 to 38085 etc. Thanks "4-8" is not a number. So Excel must make an assumption as to what it is. It could be a date, it could be text, or it could be a formula. Unfortunately, Exc...

Label with today's Date
Is there any option to include the system date on a label? Currently we code by hand on each label when the item was put on the shelf to move the inventory (oldest first). I'd like to just have the system date printed on the label so the manual step can be avoided. I can see that fields can be selected from the item table and that static text can be included but nothing for the system date. If you use RMS Label Designer with a Windows driver for your printer, Date Last Sold and Date Last Received are supported as a field entry. Last Received should work for you assuming you label t...

how do I change date from mm/dd/yyyy to dd:mm:yyyy format in Excel
I have a spreadsheet that the data, the date was input in Excel 2003 as mm/dd/yyyy (12/31/2004), how can I convert the column of cells format to "dd:mm:yyyy"(31:12:2004)? I tried to change date format from "Format Cells----choose "date" or "custom" and tried different options, but they wouldnt change the data already in the cells. I have a spreadsheet that Microsoft is requesting information in dd:mm:yyyy (yes colons) format. Thanks Jack How about =TEXT(SUBSTITUTE(E1,":","/"),"dd/mm/yy") assuming date in E1 VB...

Date Format 02-12-10
Using Windows XP and Office 2007 There is a date format that gives me the complete spelling of the day, the complete spelling of the month, the date, and the year. Friday, February 12, 2010 Can I create a date format that gives me the three letter abbreviation of the day, the three letter abbreviation of the month, the date, and the year? Fri, Feb 12, 2010 Custom format of ddd, mmmm dd, yyyy Gord Dibben MS Excel MVP On Fri, 12 Feb 2010 15:38:18 -0600, "BK" <nospam@nospam.com> wrote: >Using Windows XP and Office 2007 > >There is ...

FRx
The auditors have asked to get data for when the last date an Frx report was modified, and we could not comply, as it is not stored in FRx. Microsoft Business Solutions Support Incident Number 8518345 : Last modified date in FRx Unfortunately, there is no way to do this within the front-end of FRx. I advised that it looks like there is a "Last Modified" value that is stored within the .f32 file, but I do not know if this value is accurate. I advised that there is no way to grab this value from within FRx. I advised that you may want to enter a suggestion to add this as a...

Outlook - Exchange Rule ?
We are currently using Outlook 2000 SP3 with Exchange 2000 Standard SP3 on a W2k SP4 server. To work around the 16 gig limitation of the Information Store, I want to create a rule within Outlook to move emails messages older than 6 months to a user public folder within Exchange. The idea would be similar to the way auto-archiving works except I don't want the email to be archived in a pst file. Through the rules wizard I can move emails to a specific folder based on dates range, but these dates are static. Is there any way to either adjust a rule to work with the current date (i.e. 'al...

count results of formula, not text in formula
I would like to count the results of a formula, format is a mix of numbers and text. A B Formula in Column B is 1 8 (8),(7) ="("&A1&"),("&A3&")" 2 10 (10) =+A2, with custom formatting of (0) to show brackets 3 7 (8),(10) ="("&A1&"),("&A2&")" 4 14 5 9 There are numbers in Column A in rows 1 to 5, there are formulas in Column B. The actual formulas are the column I need to count the requirements. In this example, I need to count ...

automatically set up dates 2 weeks apart
i want to be able to make the date in a cell be two weeks difference(14 days exactly) from the cell above it how would i do this ? say that cell A1 contains a date 18/11/03 (I'm in Australia so we use dd/mm/yyyy) and you want cell A2 to be equal to that plus 14 days, ie 2/12/03. In cell A2 type =A1+14 HTH, katherine "William Storey II" <billstorey@sbcglobal.net> wrote in message news:Pgcub.32129$fD6.8383@newssvr27.news.prodigy.com... > i want to be able to make the date in a cell be two weeks difference(14 days > exactly) from the cell above it how would i do th...

Data Entry Restrictions & Date/Time Entry
I have a spreadsheet that multiple users will be using. I would like to restrict data entry to all caps. Is this possible? Also, I would like each line of the spreadsheet to display the current date & time of the entry. I would like this step to have as little of an impact on the user as possible. Any suggestions would be greatly appreciated. Thanks! You can't restrict entry to all caps - in Edit mode, any code you write will be suspended. However, you can use a worksheet_Change event macro to convert to upper case after the entry is made. But first, for how to date/time stam...

Stop auto "date" format
Additional information: - I'm using Office XP - I'm using Windows XP After I entered the formula involving with data, Excel will help me to change the cell format into "date". Is there any way to stop this automatic change? Hi You could format the cells as text first, or you could precede your entry with a ' -- Andy. "0-0 Wai Wai ^-^" <x@x.com> wrote in message news:eM8UG1ENEHA.3520@tk2msftngp13.phx.gbl... > > Additional information: > - I'm using Office XP > - I'm using Windows XP > > > After I entered the formula ...

Problem with limiting a report to a date range when dates are the same
Hi I used Allen Brown's tip for limiting a report to a date range. http://allenbrowne.com/casu-08.html It works great and is elegant and flexible. However, I just found that if I want to limit the report to a single date and enter the same date in the start and end date, I get no records even though records exist for this date. Please let me know how I can limit the report to just a single date. Thanks in advance Mark If the start date and end date are the same, the code should return the records for just that one date. If it is not doing that, chances are that your field contains...

Date format on a drop down box
I have a dropdown box that is linked to a cell where a date is put that is used for other things. The drop down box is filling itself from a range, which contains a sequential list of dates. I have verified that the dates are indeed dates and not text values. However, whenever I select a date using the drop down list, the link cell reverts to a serial format. I have tried to reformat the cell to be mm/dd/yyyy manually but without any success. Should be a simple reason but I am stumped here. Hi You have to format the cell' where you select the date, too. Formatting the data va...

Counting cell on condition
Hi, I have sheet, in which Col A contains dates, now i have to calculate the dates with the condition of >= and <=. for example: Col A 01/01/07 01/01/07 01/01/07 01/01/07 01/01/07 01/02/07 01/02/07 01/02/07 01/02/07 01/02/07 01/03/07 01/03/07 01/03/07 01/03/07 01/04/07 01/04/07 01/04/07 01/04/07 01/04/07 Now in cell B2,i have to count the dates which are between 01/01/07 and 01/02/07. Please help. thanks, navin If you have the first 01/01/07 in A1 then try =COUNTIF(A:A,A1) Hope this helps Andrew "navin" <navin.narayana@gmail.com> wrote in message news:1170263...

AVG(COUNT(timestamp))...this will not work, what am I doing wrong?
Please help me. I want to find the average of the number of timestamps that are in several tables. When i run the following, it will not work because I can only use avg with a number datatype. (at least, that's what i understand) I am just learning the syntax rules. the following works as long as i don't include avg. Please help me. Select avg( count(timestamp),area), datepart(hh,timestatmp), datepart(wd, timestamp) From lotsoftables where timestamp between 05/01/2007 and 06/20/2007 Group by timestamp,datepart(hh,timestatmp), datepart(wd,timestamp),area Thanks in advance. -Misty --...

Calculate Date and Times (based on Business days)
I have two date & time fields that I need to calcualte the number of days line is open. I have my first data point in B2 and the one to subtract is in Q2. Thanks, Diane apology but I don't understand your requirement..... -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "Diane" wrote: > I have two date & time fields that I need to calcualte the number of days > line is open. I have my first data point in B2 and the one to subtract is in > Q2. > ...

function help for counting text as a value....
I have a schedule spreadsheet. i have 7 names in column A starting at row 4 the assignments are in Column B through O representing 2 weeks. (yes i have them labeled too in rows 2 and 3 for day of the week and the date) i can't remember the formula for calculating the assignment as a value of 12. here is an example of my spreadsheet. Sun Mon Tues Wed Thurs Fri Sat Sun Mon Tues Wed Thurs Fri Sat 17-Jan 18-Jan 19-Jan 20-Jan 21-Jan 22-Jan 23-Jan 24-Jan 25-Jan 26-Jan 27-Jan 28-Jan 29-Jan 30-Jan Theresa AJ D AJ D OFF AJ D AJ D AJ D AJ D OFF OFF AJ D AJ D AJ D AJ D AJ...