Date Function Help

I need some assistance writing a formula that will count how many
dates (in a list of dates in m/d/y format) are in each month. So...

Column A [1/1/08, 2/7/08, 10/19/08, ...]
Column B [Months, Jan, Feb, Mar, ...]
Column C [Counts, {count of dates in January}, {count of dates in
February}, ...]

Make sense?

The obvious answer would be to use another column to extract the month
values and then just perform a COUNTIF function. But I can't use an
extra column.

Any help is appricated.

Thanks,
Shelton
0
shelfish (11)
3/20/2008 8:05:38 PM
excel 39879 articles. 2 followers. Follow

6 Replies
382 Views

Similar Articles

[PageSpeed] 10

Try this in C1:

=3DSUMPRODUCT(--(MONTH(A$1:A$100)=3DROW(A1)),--((A$1:A$100)<>""))

and copy down. This ignores years, so dates from January 2007 and from
January 2008 will be counted in with the January totals.

Hope this helps.

Pete

On Mar 20, 8:05=A0pm, shelfish <shelf...@gmail.com> wrote:
> I need some assistance writing a formula that will count how many
> dates (in a list of dates in m/d/y format) are in each month. So...
>
> Column A [1/1/08, 2/7/08, 10/19/08, ...]
> Column B [Months, Jan, Feb, Mar, ...]
> Column C [Counts, {count of dates in January}, {count of dates in
> February}, ...]
>
> Make sense?
>
> The obvious answer would be to use another column to extract the month
> values and then just perform a COUNTIF function. But I can't use an
> extra column.
>
> Any help is appricated.
>
> Thanks,
> Shelton

0
pashurst (2576)
3/20/2008 8:24:20 PM
On Thu, 20 Mar 2008 13:05:38 -0700 (PDT), shelfish <shelfish@gmail.com> wrote:

>I need some assistance writing a formula that will count how many
>dates (in a list of dates in m/d/y format) are in each month. So...
>
>Column A [1/1/08, 2/7/08, 10/19/08, ...]
>Column B [Months, Jan, Feb, Mar, ...]
>Column C [Counts, {count of dates in January}, {count of dates in
>February}, ...]
>
>Make sense?
>
>The obvious answer would be to use another column to extract the month
>values and then just perform a COUNTIF function. But I can't use an
>extra column.
>
>Any help is appricated.
>
>Thanks,
>Shelton

Something like:

=SUMPRODUCT(--(MONTH(Dts)*(LEN(Dts)>0)=ROWS($1:1)))

entered in the column next to where you have Jan, and fill down to Dec, will
give you a count for each month.

--ron
0
ronrosenfeld (3122)
3/20/2008 8:28:53 PM
Wow! I consider myself pretty advanced but you got me.

I'm getting a #VALUE error.

I see the two arrays in the formula, but I don't have any blanks so I
should only need the first one right?

I replaced the range you had with my actual range...no luck
I replaced the range you had with the name of the
range("claim_date")...no luck
I tried it as an {array function}...no luck...and not...no luck

Here's exactly what I have now:
=SUMPRODUCT(--(MONTH(claim_date)=ROW(A1)),--((claim_date)<>""))

Question: What do the double-dashes mean?

Thanks again!

S.

0
shelfish (11)
3/20/2008 8:48:56 PM
Ron,

That works well. Still, what function do the double dashes perform?

Many thanks for the help.
S.

0
shelfish (11)
3/20/2008 8:57:56 PM
On Thu, 20 Mar 2008 13:57:56 -0700 (PDT), shelfish <shelfish@gmail.com> wrote:

>Ron,
>
>That works well. Still, what function do the double dashes perform?
>
>Many thanks for the help.
>S.

The double dashes convert the Boolean value into a number.  In Excel TRUE
converts to 1 and FALSE to zero.
--ron
0
ronrosenfeld (3122)
3/20/2008 9:31:20 PM
It worked fine for me in my tests. Are you sure you have proper dates
in column A?

Pete

On Mar 20, 8:48=A0pm, shelfish <shelf...@gmail.com> wrote:
> Wow! I consider myself pretty advanced but you got me.
>
> I'm getting a #VALUE error.
>
> I see the two arrays in the formula, but I don't have any blanks so I
> should only need the first one right?
>
> I replaced the range you had with my actual range...no luck
> I replaced the range you had with the name of the
> range("claim_date")...no luck
> I tried it as an {array function}...no luck...and not...no luck
>
> Here's exactly what I have now:
> =3DSUMPRODUCT(--(MONTH(claim_date)=3DROW(A1)),--((claim_date)<>""))
>
> Question: What do the double-dashes mean?
>
> Thanks again!
>
> S.

0
pashurst (2576)
3/20/2008 11:11:52 PM
Reply:

Similar Artilces:

Date format issue when submitting from a userform to a spreadsheet
Hi, I have a userform that I've generated which routes dates onto a spreadsheet based on the users input. I am having a bit of a frustrating time with the dates, it would appear that in the process of moving the date from the userform to the spreadsheet some dates are switched/transposed. I'll give an example. If someone enters 09/02/2004 on the userform excel seems to look at 09 and assume it is a month and transposes the dates to 02/09/2004(this does not appear to be a US/UK format issues as I have already gone down that road). However, if the date 13/09/2004 is entered i...

Creating a group of cells. Need Help Please.
Havn't used excel in a while and I need to create a group of cell corresponding to an input of a min and a max. Here are the details. On one sheet I have a box where you enter th min and a box where you enter the max. In another sheet I want column starting at A2 to output (MIN,A2+1000,A3+1000,....MAX) ho would I do this -- Thundersix ----------------------------------------------------------------------- Thundersixx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3055 View this thread: http://www.excelforum.com/showthread.php?threadid=50207 Name the...

SUMIF function not calculating from closed worksheet
We recently upgraded from Microsoft Office 2000 to 2007. Prior to the upgrade each department would access a spreadsheet that would load data from a common lookup spreadsheet The loading process was performed as follows: The lookup file and each department file would be opened to update the link for that month and then saved and closed. When the department head would open the department file, a question would appear asking if they wanted to update the link, if they answered no the spreadsheet would open and display the calculated data. If they answered yes, the file would o...

Please help..with a formula. I don't know code.
I have a long list of numbers - values in a file X, and I want to fin and replace those values in a even larger list in a file Z an highlight those values in Z -- Message posted from http://www.ExcelForum.com Hi not really sure what you're trying to achieve. What do you want to replace, etc. You may give an example (plain text - no attachment please) >-----Original Message----- >I have a long list of numbers - values in a file X, and I want to find >and replace those values in a even larger list in a file Z and >highlight those values in Z. > > >--- >Message...

Need a default email account for all users, need help.
I have a tablet PC running WinXP Tablet with Outlook 2003. This tablet will connect to our exchange server via VPN. How can I set it up so that everyone that logs onto their account can access one (the same) email account. The problem is that I dont know at this point all of the users however anyone using the tablet will use one generic email account. So how can I set Outlook to default to this account so that no matter who logs on they will use this account? Thanks! Shane ...

Need help with formula 01-13-10
I am trying to adapt a formula in I2 from another spreadsheet that works well, but won't in mine. I've traced the error, but I would need help to understand the help it gives! My formula is this: =IF(J2="0-Jan-00","To be advised",WORKDAY(J2,1,NWD)). I have a worksheet in the same workbook with a list of non-workdays, and defined the column of dates with the name "NWD". What I expect the formula to do is this: If J2 is Feb. 4, it would give Feb. 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD. But if J2 is Feb. 5, and Feb. 6 and...

Keyboard shortcut for current date and time
Hi, Ctrl+ ; inserts current date and Ctrl+Shift+; inserts current ti me Ctrl+Shift+; inserts the current time with the date serial as 0 and not the current date's date serial. Presently I am adding the two (ie current date and time) to get the current date and time. Is there a keyboard shortcut that does this? Thanks in advance. Regards, Raj CTRL+; then SPACE then CTRL+SHIFT+; -or- =Now() -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware "Raj" <rspai9@gmail.com> wrote in message Hi, Ctrl+ ; inserts current dat...

DEADLINE... PLEASE HELP! Stacked Bar chart?
I'm not even sure how to ask the question so here's what I have... 2003 2004 2005 Actual/Goal Actual/Goal Actual/Goal Me 1009/1061 591/866 658/897 Comp. A 966/1012 633/811 624/808 Comp. B 699/744 450/593 480/607 Comp. C 957/1005 642/821 665/838 I wanto to show a bar for each competitor, for each year, so there will be 4 bars for each year. Each bar showing Actual performance & Performance Goal...

please help with this query
Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...

More than 7 If Functions Example is IF(A2="Bongaigaon", B2*40, ..
i want to use if Command in which 19 times If Function to be used for Example of the Function is =IF(A2="Bongaigaon", B2*40, IF(A2="Dhemaji", B2*100, IF(A2="Dhubri", B2*50, IF(A2="Dibrugarh", B2*65, IF(A2="Golaghat", B2*50, IF(A2="Guwahati", B2*20, IF(A2="hojai", B2*50, IF(A2="Jorhat", B2*60, If(A2="Kharupetia", B2*50, up to 19 times Hi - You really need to create a table with the 19 names in one column and, in the column to the right, the vaues associated with each name. That would ...

using dates Part 2
Karl was great in helping me get to this point with dates, now I'm wondering if we can take it 1 step further? For Activity Dates prior to 2/1/2007 they are using a normal reporting year and the formulas below take care of Activity dates >2/1/2007? So for example prior to 2/1/2007 1/1/2006 would have a B_Qtr of 2006-1 1) B_Qtr - 2011-1 --- Format(DateAdd("m",11,[ActivityDate]), "yyyy - q") 2) Year - 2011 ---- Year(DateAdd("m",11,[ActivityDate])) 3) Qtr - Q1 ---- Format(DateAdd("m",11,[ActivityDate]), "q") -- Than...

Hyperlink File Help
I am needing some major help. I have a file with hyperlinks in column F that link to a file on our server. I am needing to test to see if the file exists and if it does, copy the file to a folder in my documents called (CapturedFiles) and if it doesn't format the cell color to red. Can VBA do this and if so how? Any help would be greatly appreciated. Thanks in advance. Fileserver or webserver ? Tim On Nov 23, 7:20=A0am, Aaron <Aa...@discussions.microsoft.com> wrote: > I am needing some major help. =A0I have a file with hyperlinks in column = F that > l...

Help please user not showing in 5.5 GAL but is in exchange 2003 GA
Up until today I have been bable to add users fine and their address would appear in both the 5.5 GAL and the exchange 2003 GAL. Is a single site with 2 5.5 servers and 1 exchange 2003 server. When I add a new user now through users and computers and put the mailbox on the new exchange 2003 server the user gets his email addresses and appears in the GAL on the 2003 server but people connected the the old 5.5 servers cannot see it. When I open the 5.5 exchange admin tool again if connected to one of the old 5.5 server I cannot see the person I just created but when connected the the 20...

VLookup #VALUE! error help needed to resolve
The following is the funcation I have: =VLOOKUP(B10,'FA CC Summary Report 1141'!F$9:G$92,2,0) I have all the columns formatted the same; as in the column that the function is using to lookup is text and so is the column for this figure in order to pull back the appropriate answer. I have keyed the data instead of having links. I have replaced the final '0' with TRUE & FALSE then put it back. I have formatted the columns for text and for numbers. But I am getting the #VALUE! error in SOME of the cells NOT all of the cells. I don't know what else to d...

help with a sub
Hi, can anybody tell me why the following code fails at FormatConditions.Add Private Sub CommandButton1_Click() Dim Sh As Worksheet Dim lngLastRow As Long Set Sh = ActiveWorkbook.ActiveSheet lngLastRow = Sh.Cells(Cells.Rows.Count, "A").End(xlUp).Row Range("A4:E" & lngLastRow).Activate Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(MOD(ROW(),2)=0" Selection.FormatConditions(1).Interior.ColorIndex = 24 End Sub Thanks -- Traa Dy Liooar Jock You have an extra open paren just before MOD: &qu...

cdrom.sys corrupt in Win7
Yesterday Win7 decided to no longer show my 2 LiteOn DVDRW drives. I've tried to re-install/repair the driver (6.1.7600.16385) and everytime I get the same response = my current driver is good. BUT, then when I check with Device Manager, it shows that the drives are not working. Can anyone help me get a new cdrom.sys installed into the system32/drivers folder? Booting up with the Win7 DVD will work. But I can't find the cdrom.sys on the disk. No other repair options are there to get this fixed. Help would certainly be appreciated. I don't want to have to start all o...

Exchange 2003 SMTP QUIT
= = = = = = = = = = = = = = = = = = = = = = = = = = = PROBLEM: Problem is that OUR SERVER is sending QUIT-, instead of sending MAIL FROM: MY Server open a SMTP connection REMOTE Server says 220 .. MY Server says EHLO to REMOTE Server REMOTE Server says 250 ... MY Server then say QUIT ! (instead of MAIL FROM ....) We have::Exchange 2003 , cu SP1, pe Windows 2003.. Exchange has also IMF (spam filter from Microsoft) and Symantec Mail Security for Exchange 4.5. all PTR is installed and working OK. The SMTP Server is workin OK a while, then it start opening a lot of connections (7-10 /sec) t...

Making a template that puts the current date in the document so that does NOT change
I'd like to make a template that sets up some standard headers and formatting for new Word documents for a night school course I'm enrolled in. Among other requirements for all papers is to put the date the paper was created at the top. I'd furthermore like it so that if I need to reopen the document after creating it to say print another copy, the date written at the top will not change. In other words, when I create a new document using the template the current date is put near the top, but when I subsequently open the file for editing or reprinting it does NOT automat...

ROUND Function with other function
Hi, I have this formula: =IF(R8>0,CONCATENATE("+",R8),R8) and the results shows me for example: +11.6 but what I really want t show is +12. I've been trying to put the "Round" function at a few places in th formula but it doesn't work. The closest I got was with this formula: =ROUND(IF(R8>0,CONCATENATE("+",R8),R8),0) where it showed me 12, without the "+" sign before.... Can anyone help me ? Thank -- Outapi ----------------------------------------------------------------------- Outapin's Profile: http://www.excelforum.com/member...

Null date parameter
Hi How can I pass a null value to a date parameter in a Sub? Thanks Regards hi John, John wrote: > How can I pass a null value to a date parameter in a Sub? you have to pass it as Varian as normal data types cannot be Null: Public Sub yourSub(ADateNullable As Variant) On Local Error GoTo LocalError Dim DateValue As Date If IsNull(ADateNullable) Then Else DateValue = CDate(ADateNullable) End IF Exit Sub LocalError: If Err.Number = 13 Then ' Type mismatch. End If End Sub mfG --> stefan <-- ...

Transaction dates a day early
Hello, Using M06 with direct connect & bill pay to my bank, the downloaded transaction dates entered are a day early in Money. If I view my account through my bank's web interface, the date would show as I'd expect (e.g. 11/21) for a given transaction. But when viewing my account in M06 (again directly connected/downloaded from bank), that same transaction has a date of 11/20. All transactions from this bank seem to "post" a day early in Money. I do let Money change the transaction date from what I entered to what the bank says (that's the way I want it). I a...

input date
hi all, is this possible: i will type 011005 and then excel will automatically format it as 01/10/05 and will be treated as date? thanks. Rufino Only with VBA code or a function in another cell, there is no way with the user interface of doing this automatically -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "rufino palacol jr" <rufinojr54@gawab.com> wrote in message news:%23Ve5wjg%23EHA.3376@TK2MSFTNGP12.phx.gbl... > hi all, > > is this possible: i will type 011005 and then excel will automatically > fo...

Date Elimination
I have a worksheet with mainly dates in column A in the format of '25 Aug 2008'. Is it possible with a macro or similar to delete lines beyond a certain date (2 years hence)? Basically, I'm not interested in data more than 2 years old. This would eliminate a lot of data and make for a more viewable worksheet. try this Sub del_date() ActiveCell.Range("A1").Select Do Until ActiveCell.Value =3D "" dt =3D Date - 730 If ActiveCell.Value <=3D dt Then ActiveCell.ClearContents ActiveCell.Offset(1, 0).Select Else ActiveCell.Offset(1, 0).Select End If Loop End ...

I need help
Here is my problem. A couple of years ago I began playing around with money. But I have come to the conclusion that I really do like the services, but I now have a bunch of acct. info that should have been input in a better manner. I want to know how do I delete all of the old info so that I can start fresh. I deleted that money program once and reloaded it but it retrieved all of the old garbage that I did not want. Your help is greatly appreciated. Sincerly, Matt On Tue, 19 Aug 2003 20:58:14 -0700, "Matt" <mrisher4@hotmail.com> wrote: >Here is my problem. A c...

Help styles
With great difficulty I am now creating documents with some styles.and outline numbering Now when I copy a table from another document into this document I get 1.1.1.1 and similar numbering all over the table. How do I accomplish the copy without the numbering inside the table? afd Try this: Select the table cells and press Ctrl+Shift+N. This (re-)applies the Normal style to text. -- Stefan Blom Microsoft Word MVP "afdmello" <afdmello@hotmail.com> wrote in message news:%23R7DwkCdKHA.4952@TK2MSFTNGP06.phx.gbl... > With great difficulty I a...