Counting nubmer of months with correct year..

Hi everyone,

I have a range of cells A1:A10, lets say, that contain 
delivery date for some product.   These date can vary from 
say January to December of 2004 to 2008.  The format is" 
MM/DD/YYYY).

I like to write a formula that counts how many delivery 
dates is scheduled this month for this year.
So the idea is that in Cell B1, I will type today's date.

Then I like the formula to tell me how many delivery is 
scheduled (from the range A1:A10) that has this month and 
this year typed-in.

I tried several things but without success. Any help 
please?

Thanks
Arpa.
0
anonymous (74722)
9/7/2004 10:58:23 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
244 Views

Similar Articles

[PageSpeed] 4

=SUMPRODUCT(--(MONTH(A1:A10)=MONTH(B1)),--(YEAR(A1:A10)=YEAR(B1)))

Hope this helps!

In article <03d901c4952e$2d4b4b80$a401280a@phx.gbl>,
 "Arpa" <anonymous@discussions.microsoft.com> wrote:

> Hi everyone,
> 
> I have a range of cells A1:A10, lets say, that contain 
> delivery date for some product.   These date can vary from 
> say January to December of 2004 to 2008.  The format is" 
> MM/DD/YYYY).
> 
> I like to write a formula that counts how many delivery 
> dates is scheduled this month for this year.
> So the idea is that in Cell B1, I will type today's date.
> 
> Then I like the formula to tell me how many delivery is 
> scheduled (from the range A1:A10) that has this month and 
> this year typed-in.
> 
> I tried several things but without success. Any help 
> please?
> 
> Thanks
> Arpa.
0
domenic22 (716)
9/7/2004 11:18:51 PM
Hi Dominic,

I tried this but it doesn't appear to work. It gives me 
zero as a answer.  I've tested it with typing in the date 
that is exactly the same as cell B1 and it still gives me 
zero as an answer.
Just trying to figure out if =Month(A1:A10)=Month(B1) 
works, I only typed this formula and i got a #Value type 
error !!

Any idea please?

Thanks


>-----Original Message-----
>=SUMPRODUCT(--(MONTH(A1:A10)=MONTH(B1)),--(YEAR(A1:A10)
=YEAR(B1)))
>
>Hope this helps!
>
>In article <03d901c4952e$2d4b4b80$a401280a@phx.gbl>,
> "Arpa" <anonymous@discussions.microsoft.com> wrote:
>
>> Hi everyone,
>> 
>> I have a range of cells A1:A10, lets say, that contain 
>> delivery date for some product.   These date can vary 
from 
>> say January to December of 2004 to 2008.  The format 
is" 
>> MM/DD/YYYY).
>> 
>> I like to write a formula that counts how many delivery 
>> dates is scheduled this month for this year.
>> So the idea is that in Cell B1, I will type today's 
date.
>> 
>> Then I like the formula to tell me how many delivery is 
>> scheduled (from the range A1:A10) that has this month 
and 
>> this year typed-in.
>> 
>> I tried several things but without success. Any help 
>> please?
>> 
>> Thanks
>> Arpa.
>.
>
0
anonymous (74722)
9/7/2004 11:40:50 PM
On Tue, 7 Sep 2004 15:58:23 -0700, "Arpa" <anonymous@discussions.microsoft.com>
wrote:

>Hi everyone,
>
>I have a range of cells A1:A10, lets say, that contain 
>delivery date for some product.   These date can vary from 
>say January to December of 2004 to 2008.  The format is" 
>MM/DD/YYYY).
>
>I like to write a formula that counts how many delivery 
>dates is scheduled this month for this year.
>So the idea is that in Cell B1, I will type today's date.
>
>Then I like the formula to tell me how many delivery is 
>scheduled (from the range A1:A10) that has this month and 
>this year typed-in.
>
>I tried several things but without success. Any help 
>please?
>
>Thanks
>Arpa.

With a date, named Dt, that represents a date in the month of interest, or a
reference to some cell that contatins a Date in the month of interest:

=COUNTIF(A1:A10,">="&DATE(YEAR(Dt),MONTH(Dt),1)) -
COUNTIF(A1:A10,">"&DATE(YEAR(Dt),MONTH(Dt)+1,0))


--ron
0
ronrosenfeld (3122)
9/8/2004 12:19:21 AM
Looks like your dates are text instead of numeric values, select an empty 
cell,
copy it, select the dates, do edit>paste special and check add.
Now try the formula

-- 
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Arpa" <anonymous@discussions.microsoft.com> wrote in message 
news:7bd601c49534$1b6294e0$a501280a@phx.gbl...
> Hi Dominic,
>
> I tried this but it doesn't appear to work. It gives me
> zero as a answer.  I've tested it with typing in the date
> that is exactly the same as cell B1 and it still gives me
> zero as an answer.
> Just trying to figure out if =Month(A1:A10)=Month(B1)
> works, I only typed this formula and i got a #Value type
> error !!
>
> Any idea please?
>
> Thanks
>
>
>>-----Original Message-----
>>=SUMPRODUCT(--(MONTH(A1:A10)=MONTH(B1)),--(YEAR(A1:A10)
> =YEAR(B1)))
>>
>>Hope this helps!
>>
>>In article <03d901c4952e$2d4b4b80$a401280a@phx.gbl>,
>> "Arpa" <anonymous@discussions.microsoft.com> wrote:
>>
>>> Hi everyone,
>>>
>>> I have a range of cells A1:A10, lets say, that contain
>>> delivery date for some product.   These date can vary
> from
>>> say January to December of 2004 to 2008.  The format
> is"
>>> MM/DD/YYYY).
>>>
>>> I like to write a formula that counts how many delivery
>>> dates is scheduled this month for this year.
>>> So the idea is that in Cell B1, I will type today's
> date.
>>>
>>> Then I like the formula to tell me how many delivery is
>>> scheduled (from the range A1:A10) that has this month
> and
>>> this year typed-in.
>>>
>>> I tried several things but without success. Any help
>>> please?
>>>
>>> Thanks
>>> Arpa.
>>.
>> 


0
terre081 (3244)
9/8/2004 12:22:09 AM
Reply:

Similar Artilces:

get the latest day of the previous month
hello, i have a date variable, and i would like with VBA to get the latest day from the previous month many thanks Function LastPrevDate(Dt As Date) As Date LastPrevDate = DateSerial(Year(Dt), Month(Dt), 0) End Function Sub test() MsgBox LastPrevDate(Now) End Sub HTH. Best wishes Harald "Laurent M" <anonymous@discussions.microsoft.com> skrev i melding news:06a701c503b0$3a4b7bc0$a401280a@phx.gbl... > hello, > > i have a date variable, and i would like with VBA to get > the latest day from the previous month > > many thanks In case you specified VBA be...

FWD: Watch these corrective update from M$ Corporation
--taqhzibek Content-Type: multipart/related; boundary="sjrajwyoigtwrop"; type="multipart/alternative" --sjrajwyoigtwrop Content-Type: multipart/alternative; boundary="cfrfxggybtixigoyx" --cfrfxggybtixigoyx Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Consumer this is the latest version of security update, the "November 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install...

How to transparent editbox control works correctly...
Hi i am working on an application where i have dialog based application. On dialog there is a picture control which displays a picture. Now i want to put an edit box. Text box string is current time which i am getting from OnTimer handler. i want that editbox control should be transparent except text in it. for this i have work with following code and get succeed in OnCtlHandler of dialog if(pWnd->GetDlgCtrlID() == IDC_EDT_Isha) { pDC->SetBkMode(TRANSPARENT); hbr = (HBRUSH)GetStockObject(NULL_BRUSH); } it makes the backgound tranparent but when the value of the edit box change...

'advanced filter/unique values only' inaccurate counts
i often have to use filters to reduce a column of numbers to just it's unique values. simple enough, but unfortunately excel's advanced filter doesn't always do the job and leaves out values or just doesn't filter correctly. i'm hoping there's a simple explanation for this and the filter can be made more reliable. any thoughts? The filters are reliable, so there may be a problem with the data. For example, a space at the end of a text string can cause two cells that look the same to be extracted by the filter. Perhaps you can give an example of what's left out, ...

How to find which day of the week, this year, a birthday fall?
Hello: I have a database with employee birthdays. (I enter all the birthdays as mm/dd/1900 so no one knows how old anyone is.) I have a query I use to inform the managers what birthdays are upcoming in their department. It so happens that 2007 had the same calendar as 1900 so I didn't realize I wasn't pulling the day of the week the birthday falls for the current year. Is there an expression I need to build or a particular format I need to set? I was using the number format dddd, mm/dd. Thanks. Using DOB as the employee's birthday: Format(DateSerial(Year(Date),Month(DO...

FWD: Try correction update for Internet Explorer
--zsyimpkmgrdj Content-Type: multipart/related; boundary="dljfzstvbcot"; type="multipart/alternative" --dljfzstvbcot Content-Type: multipart/alternative; boundary="wcajnpnaismbywyg" --wcajnpnaismbywyg Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Consumer this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Instal...

Sorting dates, ignoring year
I have a speadsheet of birthdays which I would like to change to be birthdates. At the moment, everyone's birthdays are specified on one column in the format dd/mm/2002 so that I can sort the worksheet to keep the dtaes in order throughout the year. Is there an easy way to sort a column of dates but ignore the year? Or do I need to split the dates into multiple columns? -- Ian -- You could split the dates into separate columns: =month(a1) =day(a1) and =year(a1) (you wouldn't use the year column) But I'd insert a helper column with formulas like: ...

The blank cells i need to count have formilas in
How do i write a fromula to add together diffeent ranges of cells that will count blank cells even if the blank has a formula in it. f 17:f12 + g23:g30+h5:h10 = 8 using excel 2007Vista Hi Rubber, If the blank cells with a formula in them has the value of zero, use COUNTA to count the cells and COUNTIF to count the zero values in each range, then subtract the two. =COUNTA(F12:F17)-COUNTIF(F12:F17,0) or fully =COUNTA(F12:F17)-COUNTIF(F12:F17,0)+COUNTA(g23:g30)-COUNTIF(g23:g30,0)+COUNTA(h5:h10)-COUNTIF(h5:h10,0) Squeaky "Rubber 4 u" wrote: > How do i w...

Specifying source campaign in Opp., not counting as campaign respo
General usage question regarding campaigns. If we create a campaign, then specify that campaign as the source campaign for an opportunity, that opportunity/account is not then listed as a response to that campaign. So when we run a Campaign Performance report, no responses are listed. Do you have to use the normal method of entering a response within the campaign? I'd think that if you can associate a campaign within the opportunity, that linkage would reflect in a Campaign Performance report. Thanks!! ...

Correcting Page Numbering with Section Breaks
Hi Community, How do I correct the page numbering in a Word document with section breaks? I inadvertently remove the first page number directly from the footer and now all pages have the same number. The first page of the document is a cover page; the second page contain the abstract and on which I would like to start the page numbering at 1. I have created two section breaks--one between the first and second pages, and one between the second and third pages. This was done to untie formatting between the sections. Thank you in advance for your assistance. In Word, the pa...

How to count last name letters
Hi folks, I would like to know how I can count how many records have the same letter. For example, how many records start with the letter A, how many for letter B, etc. Seems simple enough, but I can't figure out how to work this. Would appreciate some help. Thanks. In news:uKD40b2rHHA.3484@TK2MSFTNGP05.phx.gbl, jason <jasonsantos-NOSPAM-@allstatesmedical.com> wrote: > Hi folks, > I would like to know how I can count how many records have the same > letter. For example, how many records start with the letter A, how > many for letter B, etc. > > Seems simple ...

Difference in years and months between 2 dates
Hi, Does anyone know how I can get the number of years and months between 2 dates? (Using Office 2000) Thanx Subtract one from the other "Els" <Els@discussions.microsoft.com> wrote in message news:E54C0D76-1313-40EF-922C-FCB0780AD992@microsoft.com... : Hi, : : Does anyone know how I can get the number of years and months between 2 : dates? (Using Office 2000) : : Thanx Hi see http://www.cpearson.com/excel/datedif.htm >-----Original Message----- >Hi, > >Does anyone know how I can get the number of years and months between 2 >dates? (Using Office 200...

How do I add the next month to a calendar w/o loosing cust format
Created a calendar page for the office with custom logo and address info. The calendar wizard changes everything back to the default when changing the date range. Help file says to add dates to retain custom elements but not 'How' to add dates. What's the best way to create a custom calendar and retain the formatting? The address lines and the logo are determined by your personal information. Look under Edit, the personal information is the very last item in the menu. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com ...

Count unique occurrences in a list
Hi guys, I have a list as so: Part1 Run1 Part1 Run1 Part2 Run1 Part3 Run1 Part4 Run2 Part4 Run2 Part1 Run3 Part2 Run3 Part5 Run3 Part6 Run4 Part1 Run5 Part1 Run5 Part1 Run5 Part6 Run5 .... Part1 Run1001 Part3 Run1001 Part3 Run1001 .... The number of unique parts may be >100 and the number of runs can be anything (it's actually ~2000 in my current list) What I would like to do is this: For each Part, count how many Runs contained that Part (that is the number of unique runs containing at least of that Part.) I should end up with a new list similar ...

Counting with 2 criteria
I need to be able to pull counts from a frequently updated list based on associate's name and status of order. -- Amethyst =SUMPRODUCT(--(A1:A100="associate name"),--(B1:B100="status")) should do it for you, adjust the columns/rows to match your sheet. You could have a couple of cells to enter associate name/status into to make things easier; as associate name in C1 and status in D1: =SUMPRODUCT(--(A1:A100=C1),--(B1:B100=D1)) "Amethyst" wrote: > I need to be able to pull counts from a frequently updated list based on > associate&#...

Count partial matched text string
I have 3 spreadsheets. The first, “Supplies Requests Received” where Column B contains the (duplicated) names of internal offices placing supply requests (e.g., SNF, DGEN, CANM, etc.) and Column G contains each date a request for supplies was received from that office. Column B Column B SNF 1//29/2009 DGEN 4/19/2008 CNMN 2/4/2009 ASEV 12/11/2008 SNF 2/3/2009 ASEV 9/16/2008 DGEN 5/1/2008 The second spreadsheet “Supplies Delivered” where Column A is a link of Column B from the above “Supplies Requests Received spreadsheet and Column G (of Supplies Delivered”) contains the names of ALL ...

correct date/days in a template
I have found a template in M/S excel download that I would like to use but how do I change the dates. The current template is dated 11/1/2004 -11/14/2004 The template is a biweekly time card which has to be changed every 2 wks and additional days need to be added for when the the starting date starts. This is the link if you care to look at it to help understand what I am trying to explain http://office.microsoft.com/en-us/templates/TC060888761033.aspx?CategoryID=CT063469961033 Thank-you have a safe 4th hi, did you try to change the 11/1/2004 to 11/15/2004? if yes change the day ...

Partial Year Close
Hello! We are on calendar year but we need to do another close as of January as we got acquired. What's the best way to do this? We tried manually zeroing out P&L accounts as of 1/31/2010 but now when we print a TB as of January it's zero. Thanks!!! One option is to just go into Fiscal Period setup and change the year to reflect the dates of your short year. Set up the next year to have the remaining months and just go from there. Your 'year' id will be 1 year ahead, but your reports will be correct. "Marisol" wrote: > Hello! We are on c...

Input Fiscal Year.
--____HTOKXXENPBKFQUMEICPN____ Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: base64 Content-Disposition: inline; modification-date="Wed, 25 Mar 2008 06:21:56 -0500" SSd2ZSBiZWVuIHRyeWluZyB0byBjcmVhdGUgYSBmb3JtdWxhIHRoYXQgd291bGQgY29udmVydCBh IGRheSBpbnRvIHRoZSBjdXJyZW50IGZpc2NhbCB5ZWFyLCBGb3IgaW5zdGFuY2UsIEp1bHkgMXN0 IGlzIHRoZSBiZWdpbm5pbmcgb2YgZmlzY2FsIHllYXIgMDgsIHNvIGlmIGNlbGwgYjMgcmVhZHMg Ny8xNS8wNywgSSB3b3VsZCBsaWtlIGNlbGwgYTMgdG8gcmVhZCBGWSAwOC4gDQoNClRoYW5rcyBp biBhZHZhbmNlLA0KRGF2aWQ= --____HTOKXXENPBKFQUMEICPN____ Content-Type: multipart/related; boun...

Counting rows based on criteria in multiple cells
I need to count the number of rows where cell A includes a specific text string AND cell B equals a specific number. I am having trouble finding a working solution. Example Column A Column B Jim cleaned 1 Bob paints 1 Cleanup, Sue's 2 Jim swept 1 Cleaning by Joe 1 I want to count the rows where Column A contains the text "clean" AND the value in Column B is 1. Correct answer from example above would be 2. Thanks in advance for any suggestions. =SUMPRODUCT(--(ISNUMBER(SEARCH("clean",A1:A100))), --(B1:B100=1)) -- HTH R...

Month Formula
Please advise me on how to write a formula that will start with January 2003, when I copy it to the next row it will increase the month to February and so on. When I get to December 2003, the following month will be January 2004. Thank You Hi msw! A1: 1-Jan-2003 A2: =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) Copy Down Select the range of cells Format > Cells >Custom format Type in the text box mmmm yyyy OK -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. ...

monthly calendar #3
Diane - I rebooted my computer and you were absolutely right! The calendar is back to normal - thank you so much! I have been working on this for days! Thanks again. ...

months between 2 dates!!!
A few weeks ago I posted a thread asking for a formula to count number of months between 2 dates with partial months counted as whole months. Here is what my spreadsheet looked like: START END TOTAL DATE DATE MONTHS 1/1/2005 4/29/2005 4 The formula I used is as follows: =IF(I51="","",MONTH(I51)-MONTH(H51)+1) Now in actually using the sheet I found that this formula is basically useless when the difference between the 2 dates exceeds a year for example: START END TOTAL DATE DATE MONTHS 1/1/2005 4/29/2006 4 Does anyone know wh...

Pivot table: Counting/Labeling row numbers
How can I get each row number to be labeled within the pivot table...and also show a total row count along with the "grand total"? Example: 1, Apple, $3 2, Orange, $4 3, Banana, $1 Grand Total, 3 (rows/items), $8 Thanks... You could insert a column to the left of the pivot table, and calculate the count there. For example, in cell A5: =IF(B5="","",IF(B5="Grand Total",MAX(A$4:A4),IF(ISNUMBER(SEARCH("Total",B5)),"",MAX(A$4:A4)+1))) Copy the formula down as far as required to accommodate the longest configuration of the pivot t...

360 day financial year
hi, Could GP accomodate a 360 day financial year? Or can it only be 365 or 366? Thanks Mark Mark, GP can accommodate 360 days. -- Victoria Yudin Dynamics GP MVP "markl" <mark@showcomplex.com> wrote in message news:1165926513.455083.66200@l12g2000cwl.googlegroups.com... > hi, > > Could GP accomodate a 360 day financial year? Or can it only be 365 or > 366? > > Thanks > Mark > Richard, Thanks, now I don't have to ask the same question. :-) -- Lyle U Richard L. Whaley wrote: > Automatically, when you calculate the dates, GP will c...