Grouping dates by month and year

I have a spreadsheet with a range of dates in one column, and a list
of statuses in the next (basically it is a list of orders, which are
either open or closed).

I want to create a list of open orders broken down by month.

i.e. Some kind of array formula which goes down A1:A200 looks for any
from say March 2010, then looks to B1:B200 for those marked as =91Open=92,
and returns a count.

It=92s been an age since I=92ve taxed my brain with this kind of stuff
(been out of work) and I=92m sure this is probably quite easy. However,
I=92ve falling at the first hurdle, trying to find a function to match
month and year (MONTH only identifying the month number 1-12)

All insight gratefully received.
0
7/26/2010 2:40:08 PM
excel 39879 articles. 2 followers. Follow

7 Replies
904 Views

Similar Articles

[PageSpeed] 56

Try this:

=3DSUMPRODUCT((TEXT(A$1:A$200,"mm yyyy")=3D"03 2010")*(B$1:B$200=3D"Open"))

You could, of course, put "03 2010" in a cell and refer to that cell
in the formula, and thus using other cells in that column for other
months you can just copy the fomula down to get a breakdown over
several months.

Hope this helps.

Pete

On Jul 26, 3:40=A0pm, Iain <spamfromgro...@hotmail.co.uk> wrote:
> I have a spreadsheet with a range of dates in one column, and a list
> of statuses in the next (basically it is a list of orders, which are
> either open or closed).
>
> I want to create a list of open orders broken down by month.
>
> i.e. Some kind of array formula which goes down A1:A200 looks for any
> from say March 2010, then looks to B1:B200 for those marked as =91Open=92=
,
> and returns a count.
>
> It=92s been an age since I=92ve taxed my brain with this kind of stuff
> (been out of work) and I=92m sure this is probably quite easy. However,
> I=92ve falling at the first hurdle, trying to find a function to match
> month and year (MONTH only identifying the month number 1-12)
>
> All insight gratefully received.

0
pashurst (2576)
7/26/2010 2:55:24 PM
On 26 July, 15:55, Pete_UK <pashu...@auditel.net> wrote:
> Try this:
>
> =3DSUMPRODUCT((TEXT(A$1:A$200,"mm yyyy")=3D"03 2010")*(B$1:B$200=3D"Open"=
))
>
> You could, of course, put "03 2010" in a cell and refer to that cell
> in the formula, and thus using other cells in that column for other
> months you can just copy the fomula down to get a breakdown over
> several months.
>
> Hope this helps.
>
> Pete
>
> On Jul 26, 3:40=A0pm, Iain <spamfromgro...@hotmail.co.uk> wrote:
>
>
>
> > I have a spreadsheet with a range of dates in one column, and a list
> > of statuses in the next (basically it is a list of orders, which are
> > either open or closed).
>
> > I want to create a list of open orders broken down by month.
>
> > i.e. Some kind of array formula which goes down A1:A200 looks for any
> > from say March 2010, then looks to B1:B200 for those marked as =91Open=
=92,
> > and returns a count.
>
> > It=92s been an age since I=92ve taxed my brain with this kind of stuff
> > (been out of work) and I=92m sure this is probably quite easy. However,
> > I=92ve falling at the first hurdle, trying to find a function to match
> > month and year (MONTH only identifying the month number 1-12)
>
> > All insight gratefully received.- Hide quoted text -
>
> - Show quoted text -

Pete,

Thanks for the reply. However, all I'm getting returned using this
formula is 'TRUE'....

Not sure why this would be, but in any case, I was thinking I'd want
to use COUNTIF, rather than SUM/SUMPRODUCT
0
7/27/2010 12:33:42 PM
Check your formula - you may have missed one of the brackets.

Countif can only be used for one condition, but you have two.

Hope this helps.

Pete

On Jul 27, 1:33=A0pm, Iain <spamfromgro...@hotmail.co.uk> wrote:
>
> Pete,
>
> Thanks for the reply. However, all I'm getting returned using this
> formula is 'TRUE'....
>
> Not sure why this would be, but in any case, I was thinking I'd want
> to use COUNTIF, rather than SUM/SUMPRODUCT
0
pashurst (2576)
7/27/2010 1:09:18 PM
On 27 July, 14:09, Pete_UK <pashu...@auditel.net> wrote:
> Check your formula - you may have missed one of the brackets.
>
> Countif can only be used for one condition, but you have two.
>
> Hope this helps.
>
> Pete
>
> On Jul 27, 1:33=A0pm, Iain <spamfromgro...@hotmail.co.uk> wrote:
>
>
>
>
>
> > Pete,
>
> > Thanks for the reply. However, all I'm getting returned using this
> > formula is 'TRUE'....
>
> > Not sure why this would be, but in any case, I was thinking I'd want
> > to use COUNTIF, rather than SUM/SUMPRODUCT- Hide quoted text -
>
> - Show quoted text -

Pete,

You are right! I thought I'd done a simple copy 'n' paste but
obviously not...

I still have a problem though, when I try and replace the "03 2010"
with a cell reference.
0
7/27/2010 1:45:38 PM
Suppose you put 03 2010 in cell D2 (note the space between 03 and
2010). Then the formula becomes:

=3DSUMPRODUCT((TEXT(A$1:A$200,"mm yyyy")=3DD2)*(B$1:B$200=3D"Open"))

You could put other month/years in the same format in D3, D4, D5 etc,
and just copy the formula down to get counts for those months.

Hope this helps.

Pete

On Jul 27, 2:45=A0pm, Iain <spamfromgro...@hotmail.co.uk> wrote:
>
> Pete,
>
> You are right! I thought I'd done a simple copy 'n' paste but
> obviously not...
>
> I still have a problem though, when I try and replace the "03 2010"
> with a cell reference
0
pashurst (2576)
7/27/2010 1:53:40 PM
On 27 July, 14:53, Pete_UK <pashu...@auditel.net> wrote:
> Suppose you put 03 2010 in cell D2 (note the space between 03 and
> 2010). Then the formula becomes:
>
> =3DSUMPRODUCT((TEXT(A$1:A$200,"mm yyyy")=3DD2)*(B$1:B$200=3D"Open"))
>
> You could put other month/years in the same format in D3, D4, D5 etc,
> and just copy the formula down to get counts for those months.
>
> Hope this helps.
>
> Pete
>
> On Jul 27, 2:45=A0pm, Iain <spamfromgro...@hotmail.co.uk> wrote:
>
>
>
>
>
> > Pete,
>
> > You are right! I thought I'd done a simple copy 'n' paste but
> > obviously not...
>
> > I still have a problem though, when I try and replace the "03 2010"
> > with a cell reference- Hide quoted text -
>
> - Show quoted text -

Aaaahhh! I'd had D2 populated with a date in MMM YY format, but what
you're doing is a text look-up derived from a cell with a formated
date.

Many thanks for your help.
0
7/27/2010 2:36:09 PM
You're welcome, Iain - thanks for feeding back.

Pete

On Jul 27, 3:36=A0pm, Iain <spamfromgro...@hotmail.co.uk> wrote:
>
> Aaaahhh! I'd had D2 populated with a date in MMM YY format, but what
> you're doing is a text look-up derived from a cell with a formated
> date.
>
> Many thanks for your help.
0
pashurst (2576)
7/27/2010 3:29:16 PM
Reply:

Similar Artilces:

Adding on Option to an Option Group
I have created a form to give a report of 3 tables and give the user the ability to sort on a column by checking the check box on the header. I had to add another field to one of the tables and now need to add this to the option group.. how? Thanks Sue Found it - for others, you need to add the field to the record source for the form, then with the option group selected, open the field list and drag the new field onto the option group (at least I hope that is it) Sue (Sometimes it actually pays off to have a stack of books over a foot high on Access) "Design by Sue" wrote...

Do you have a useful monthly calendar print template for Outlook
Hi. The monthly printout from Office 2003 was good. The monthly template from Office 07 ends up just having the times. Does anyone have a useful monthly template? Thanks. there are some on Microsoft's web site. Just search "Microsoft Outlook 2007 home page" to find a stack of resources. Regards Judy Gleeson MVP Outlook in Canberra, Australia "RSC" <RSC@discussions.microsoft.com> wrote in message news:D1E6422A-8D4A-4995-AA4A-3048242EED4B@microsoft.com... > Hi. The monthly printout from Office 2003 was good. The monthly template > ...

Date Formats for Cells Do not match System Date Formats
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have just loaded Office 2008 and then update to the latest update 12.2.4 <br> I have my system preferences set to Australia for date and time using the date format dd/mm/yyyy. But in Excel when I open the format cell dialogue and select date the only formats available for selection are in the mm/dd/yyyy formats. When I checked in &quot;Numbers&quot; the dd/mm/yyyy format is used so &quot;Numbers&quot; is picking up the system preference why not the excel. Did you by any chance have ...

Pivot tables with pre-counted dates
Hello I wish to build a pivot table, based on a simple data sheet, with the following columns: a) name b) type c) price d) date e) place My problem is that "date" column contains individual dates in rows, for example 2003-07-29, 2003-07-30, 2003-07-31, 2003-08-01 etc., and this is displayed in the pivot table. What I exactly want is to pre-count these dates into months (2003-07, 2003-08 and so on), of course with aggregating individual prices withing a single month. I know I can write a macro, that will generate another column and treating the date as string, cut the last 3 ...

Group Policy removing CRM Add-on from Outlook
One of our CRM users can no longer access any of the buttons for CRM in Outlook (they can access CRM, but not the "track in CRM" or advanced find etc). Tools > Options > Other > Adv Options > COM Add-Ins does not show CRM, so I had to grant the user local admin rights, so that they could then re-add the COM add-in, which makes everything work. However, as soon as the user logs off and back in, the COM Add-In is gone. What could be possibly be removing this COM add-in? ...

Group by for quartiles
I have a dataset that I need to calculate quarties for. This data set contains about 20K rows with 200 or so job titles. I want to be able to calculate the first quartile for each job title. I have seen some posts on calculating the quartile for a given dataset, but not on how to do for multiple categories, in this case, job titles. Thoughts? PJ I assume you want to get the limit of the first quartile, per job. I would define the first quartile value as the minimum value that is still in the TOP 25 PERCENT largest values I doubt that a TOP 25 percent would be fast enough, so I wou...

Add group to report with out losing details like in MS Access
I need to create a report where I list jobs including customer name and invoice amount. I want to group by customer where all the jobs for the customer are listed in a group, and at the bottom of each group I want an invoice sub total for the customer group. then at the end of the report (at the bottom of all the groups and not the report footer) I want an invoice grand total for all groups. This is a no-brainer in MS Access, but doesn't seem possible in ssrs. when ever I create a group all the rows for each customer are reduced to one row (losing the details). this is v...

Date Diff
> I am new to Access.I work for a multi billion $ company and believe it not > have no expertise in Access.We have Access 2002.I created a table which has 2 > date fields.I would like to obtain the date difference excluding weedends.I > would like to know the formula and how and where to use it. > -- Insurance Guy Do a search on Weekdays. You'll find numerous threads relating to your problem. Bob PJ wrote: >> I am new to Access.I work for a multi billion $ company and believe it not >> have no expertise in Access.We have Access 2002.I created a table which ...

Using "Filter" to isolate particular dates
We have an Excel document with a column showing dates of annual reviews for employees. Through the Filter function is there a way to isolate annual reviews due in a particular month? Thanks for any suggestions. (I know we can sort the entire document by date, but I'd like to show only those reviews that are coming due.) Hi, this is DanMcC again I did think of one solution, which is to determine numical value of 1st and last dates of the month in question. Then, use those numerical values in my Filter equation to isolate that month. A bit convoluted, but it works.... Are you using an...

Grouped Sheets and Formating
I recorded the following macro to undertake formatting of certain columns to autofit and centre certain columns. Columns("A:A").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("K:K").EntireColumn.AutoFit Range("K1").Select With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .MergeCells = False End With Columns("C:C").Sel...

How to link an Excel file due date to Outlook calendar date?
I have a chart that displays two important due dates. I'd like to link them to Outlook calendar. ...

Stamp date for every record change
Hello! I need to know when and who made a change to a record. Is there an easy way to do this? The problem is that the data can be changed through both forms and queries, so programming the event for every form/query is impractical. Is there any 'global' event that fires when a record is saved or something? In Access 2010 (released later this month), you will be able to use Data Macros to do this. In current versions of Access, there's now way. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html...

sum month
I am working in the building and have to calculate the time span each tenant stayed . I have "date in" and "date out" and "time spent" columuns. Does anyone can give the formula for "time spent" column? Thanks! LJ Hi assume Date in is cell B2 Date out is cell C2 formula for D2 =C2-B2 format as number to get the number of days that the tenant stayed. Cheers JulieD "LJ" <mhgk@excite.com> wrote in message news:OQzaV3j0EHA.2012@TK2MSFTNGP15.phx.gbl... >I am working in the building and have to calculate the time span each >...

Subtracting exactly 1 calendar month from a task
In my project, I've setup a bunch of milestone dates (duration: 0 days), for example - the announce date for a product, which is April 30, 2010. I have some tasks in my project plan which require things to be done by exactly 1 month (or 30 days) prior to the announce date. I've also set these up as zero-duration milestones. However, when I set up my predecessor lag times as FS or FF minus 1 month or 30 days, I get March 19, 2010. Does this have to do with working time? I really just need it to backtrack roughly a month plus or minus a day - so March 30 or 31. Any id...

Increment date by 12 months
I would like to create a formula to increment a date in one cell by 12 months in the cell below. For example, in cell a1 the date is jan 1/2004. I want a formula to automatically calculate the date in cell a2 to be jan 1/2005 (increase of twelve months). In Access you can use DateAdd. Is there something I can do in Excel? Attila Hi try =DATE(YEAR(A1)-1,MONTH(A1),DAY(A1)) note: this may give you a wrong result for 29-Feb-2004 in cell A1 -- Regards Frank Kabel Frankfurt, Germany Attila Fust wrote: > I would like to create a formula to increment a date in > one cell by 12 m...

How to Format Date of a Timeline
Is it possible to format the date for the intervals on a timeline as follows (Swiss German Format)? Mi. 12.11.2008 I' ve configured the Date format manually under -> Define -> Define Shape Data like this: ((ddd. dd.MM.yyyy)) But it doesnt work... ? :-( Thanks, Andi On Fri, 14 Nov 2008 12:52:15 +0100, "msnews.microsoft.com" <anaef@ananet.ch> wrote: >Is it possible to format the date for the intervals on a timeline as follows >(Swiss German Format)? > >Mi. 12.11.2008 > >I' ve configured the Date format manually under -> Define -> ...

dates suddenly changed from x/xx/2004 format to number
I set up my excel 2003 for dates to be written 4/25/2004 and instead all I get is serial numbers. I tried to format the cell for the date as above but it only comes out in serial numbers. When I set this spead sheet up it worked fine. The suddenly this changed and the dates went away. I cannot reverse this. any hints. As answered by Jim Rech in excel.misc -- Maybe you hit Ctrl-~ by accident and changed to "formulas view"? Hit it again or uncheck Tools, Options, View, Formulas. scheinz wrote: > I set up my excel 2003 for dates to be written 4/25/2004 > and instea...

How do I turn off the updating of the date on an excel invioce
If i pull up an invoice to reprint it the date changes to the current date How do i turn this feature off? Put in the date manually by using Ctrl + ; in the first place when you create the invoice I assume you have either TODAY() or NOW() and they will update with each calculation -- Regards, Peo Sjoblom "Isoking555" <Isoking555@discussions.microsoft.com> wrote in message news:1FCCD834-AF53-48A7-B8C8-37B6DC329EDF@microsoft.com... > If i pull up an invoice to reprint it the date changes to the current date > How do i turn this feature off? ...

Strange Date Format Bug
Hi all, I don't know which subtopic this belongs to. Each time I work with dates in Excel 2003 on my machine, the date, regardless of the format, becomes a number. For e.g. When I type the date 1/1/2006, the Formula Bar shows me, 1/1/2006, but the column actually shows "38718". A different number for each date. I've tried all Date formats in regional settings, nothing works. Can anyone help? Best wishes, Shiva You should format the cell (or column) - Format | Cells | Number (tab) and choose Date, then you will be given different styles that you can choose from - pick ...

calculate how many sundays between two dates in excel #2
calculate how many times sundays occurs between two dates in excel Hi Jeff, =SUM(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=1)) array-entered HTH, Bernd ...

Changing Date Values!!! (dd/mm/yy) =(
Hi everyone.. im new to this forum :rolleyes: i joined because i wanted a solution to something thats been bugging me ever since i got Excel :eek: I am running Micrososft Excel 2003 in case you wanted to know... The problem is this: When i type a date in the format: (xx/yy/zz) - Excel recognises the xx as the month (ie. mm/dd/yy) When in fact i want it to recognise xx as the day (ie. dd/mm/yy) That means every time i type (1/2/05) into a1 and (2/2/05) into a2 i drag the rest and they come out like this: 1/2/2005 2/2/2005 3/2/2005 4/2/2005 5/2/2005 6/2/2005 7/2/2005 8/2/2005 9/2/...

font does not restore after entering date
Example: When I type the text December 28th in Ariel 11 the th automactically reduces to a smaller font and when I tap space bar to continue it is suppose to revert back to the lager font , most of the time it just continues to type at that small font why is it being so Inconsistent. I recently reformatted my hard Drive and when I reinsatlled office this proble still continued. 2nd question. When I post a question and check the "Notify me of replies" box I never receive a reply I always have to go the the site to search my response. I deleted my profile an...

Date Formulae
Hi People, Am looking for a formulae that will do the following: First: calculate the number of days between two dates. Second: Calculate the number of working days between to dates, were th working days are monday to friday. Thanks Paul p.s the new look is goo -- paulu http://www.frontlineuk.co ----------------------------------------------------------------------- pauluk's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=520 View this thread: http://www.excelforum.com/showthread.php?threadid=26379 Hi 1. =A2-A2 and format this cell as number 2. =NETWORKDAY...

Date Picker
Hello, Is there a VBA method to bring up a calander without having to click the DatePicker icon for a date control? Thank you and God Bless, Mark A. Sam Not sure of exactly what you have in mind, but here's a routine that I use:. If you'd like to only have the DatePicker appear when you need to pick a date, you can use this routine. YourTextBoxName is the name of the box that will hold the date YourDatePickerName is the name of your DatePicker. First, place the DatePicker where you want it to appear on the form. Next, select the DatePicker and goto Properties--Format and...

dates change when I copy and paste
I have a report that has columns for Day of week and a column for th date(10/15/04) but when I copy and paste to another excel worksheet th date changes from ex: 10/15/04 to 10/13/08. Any ideas why this woul happen. It's a pretty lengthy report and I hate to always have t change each date. Help. : -- Nesi ----------------------------------------------------------------------- Nesia's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1534 View this thread: http://www.excelforum.com/showthread.php?threadid=26965 Hi both workbooks have different date setting (...