Current month question

[Brandy]

My data appears as such:

1/2/2004

It must stay in this format.

The information I need is how many referrals were made
only for the month of July using this information.

Any ideas on how I might accomplish this?

 

[JulieD]

Hi Brandy

try
=SUMPRODUCT((TEXT(A2:A27,"mmm")="Jul")*1)
OR
=SUMPRODUCT(--(TEXT(A2:A27,"mmm")="Jul"))
OR
=SUMPRODUCT(--(TEXT(A2:A27,"mmm")=TEXT(NOW(),"mmm")))

where A2:A27 contains your dates

Cheers
JulieD

 

[Domenic]

Hi Brandy,

Assuming that your dates are contained in Column A, and each date
represents a referral:

=SUMPRODUCT(--(MONTH(A1:A100)=7),--(YEAR(A1:A100)=2004))

Hope this helps!

 

[Norman Harker]

Hi Brandy!

Try:

=SUMPRODUCT(($A$1:$A$200<>"")*(TEXT($A$1:$A$200,"mmm")="Jul"))

I have to adjust for the possibility of blanks to cover you wanting
January because they are treated as 0 and the Date Serial number of 0
is treated as Sat, 1 Jan 1900 and is thus in the January total. I'de
used a cell reference for the short month name and it is not difficult
to enter Jan in I1 and then copy down (pressing Ctrl) to get the 12
months of data. However, a pivot table might be the prefered solution
for that.

Note that if the data includes different years, the formula will
return July total for all years.

 

[Frank Kabel]

Hi Julie

I would use the MONTH function
So to add an alternative:

=SUMPRODUCT(--(MONTH(A2:A27)=MONTH(NOW())))

 

[JulieD]

Hi Frank

thought about that one too but i had a document open using the TEXT function
(and it worked) so i just copied & pasted that

Cheers
JulieD