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 |
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 |
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 |
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 |
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 --- |
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 |
All times are GMT +1. The time now is 03:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com