Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
MONTH function where cell is blank
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
MONTH function where cell is blank
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
MONTH function where cell is blank
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 "Lmurraz" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
MONTH function where cell is blank
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). "Rick Rothstein (MVP - VB)" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
MONTH function where cell is blank
"Lmurraz" wrote:
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(...))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
MONTH function where cell is blank
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 "Lmurraz" wrote in message ... 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). "Rick Rothstein (MVP - VB)" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Force date cell to show [blank] if end of month | Excel Discussion (Misc queries) | |||
Formula/Function to insert month by name in a cell | Excel Worksheet Functions | |||
IF function for blank cell | Excel Worksheet Functions | |||
When using MONTH function on Blank Cell!! Returns Month=Jan! | Excel Discussion (Misc queries) | |||
If Function: Blank cell and 0 (zero) seem to have same "value" | Excel Worksheet Functions |