MONTH function where cell is blank

[Guest]

I am trying to create a formula whic will allow me to count how many things
happened in a particular month. I have successfully got the formula to scan
the worksheet to return the month code (which I then run a second formula
across to count the occurrences). The problem I have is that when Excel
encounters a blank cell it returns a month value of "1" (I assume this is the
default date of 1-Jan-1900). How do I stop this? I would ideally like to
return a message "date not found" in this instance.

Thanks

 

[Rick Rothstein \(MVP - VB\)]

I am trying to create a formula whic will allow me to count how many

things
happened in a particular month. I have successfully got the formula to
scan
the worksheet to return the month code (which I then run a second formula
across to count the occurrences). The problem I have is that when Excel
encounters a blank cell it returns a month value of "1" (I assume this is
the
default date of 1-Jan-1900). How do I stop this? I would ideally like
to
return a message "date not found" in this instance.

You didn't show us your function or the range you are working with; hence,
if my guess is wrong, you will need to adjust the formula...

=IF(A1<>"",MONTH(A1),"Date not found.")

Rick

 

[Guest]

Try:

Dates in A2 to A100

=SUMPRODUCT(--(MONTH(A2:A100)=6),--(A2:A100<>""))

will count occurences of month 6 (June) for ALL years in your data

=SUMPRODUCT(--(MONTH(A2:A100)=6),(--(YEAR(A2:A100)=2007),--(A2:A100<>""))

June 2007 only

SUMPRODUCT cannot have a complete column e.g. A:A, unless you have Excel 2007.

HTH

 

[Guest]

My current formula is:-
=MONTH(VLOOKUP(A1,'Y:\EWO Tracking\Mill & DSG\[Mill & DSG EWO
Tracking.xls]Mill'!$1:$65536,7,0))

This gives me the month in a numerical form. I was then going to use COUNT
or SUMPRODUCT to count the number of times a particfular month appears. The
source spreadsheet will be annual (Separate sheets per year).

 

[Guest]

My current formula is:-
=MONTH(VLOOKUP(A1,'Y:\EWO Tracking\Mill & DSG\[Mill & DSG EWO
Tracking.xls]Mill'!$1:$65536,7,0))

Try trapping the vlookup's return for zeros, viz something like:
=IF(VLOOKUP(...)=0,"date not found",MONTH(VLOOKUP(...)))

---

 

[Roger Govier]

Hi

I think I would be inclined to put a formula on the source sheet in a
currently unused column
=SUMPRODUCT(--(MONTH($G$1:$G$65535)=ROWA(A1)),--($G$1:$G$65535<>""))
Copy down for a further 11 rows.
Then read from whichever of the 12 cells on that sheet, to get the
number of entries for that given month.

Or you could enter Jan, Feb Mar .... Dec in 12 cells of an unused column
(have used column AA in this example), and in the next column to them
enter
=SUMPRODUCT(--(TEXT($G$1:$G$65535,"mmm")=TEXT(AA1,"mmm")),--($G$1:$G$65535<>""))

To get your result then
VLOOKUP(A1,'Y:\EWO Tracking\Mill & DSG\[Mill & DSG EWO
Tracking.xls]Mill'!$AA$1:$AB$12,2,0)
where A1 = Jan
--
Regards

Roger Govier

My current formula is:-
=MONTH(VLOOKUP(A1,'Y:\EWO Tracking\Mill & DSG\[Mill & DSG EWO
Tracking.xls]Mill'!$1:$65536,7,0))

This gives me the month in a numerical form. I was then going to use
COUNT
or SUMPRODUCT to count the number of times a particfular month
appears. The
source spreadsheet will be annual (Separate sheets per year).

You didn't show us your function or the range you are working with;
hence,
if my guess is wrong, you will need to adjust the formula...

=IF(A1<>"",MONTH(A1),"Date not found.")

Rick