When using MONTH function on Blank Cell!! Returns Month=Jan!

When i use the MONTH(A3) Function and A3 is blank, excel takes it as a
0! ie. MONTH(0) and returns January as the month because the date in
serial is 00-Jan-1900. How do i get around this! Here is my Formula:

I am trying to count the number of months that are used, i have done
this with this formula below: It works perfect for all months except
for Jan! because it think that blanks cells are a serial date 0.

{=SUM(IF(MONTH(DATA!A2:A7)=1,1,0))}

__________________
|   A                    |
1 | Date                  |
2 | 01-01-05            |
3 | 04-04-05            |
4 | 04-04-05            |
5 |                         |
6 | 01-01-05            |
7 |                         |
----------------------

* T h e   a b o v e   f o r m u l a   a n d   d a t a   r e t u r n s 
J a n   C o u n t   =   4 ,   b u t   a s   y o u   c a n   s e e   t h
e r e   i s   o n l y   2   J a n u a r y ! *   
 
: m a d : : c o n f u s e d :   
 
P L E A S E   H E L P !   L o l 

I have tried to use the ISBLANK function but i could not work out how
to incorporate it into my problem.

I did Ctrl - Shift - Enter to do an array also as you can see by
bracets..

I also Attach a pic of my problem~ Hope it helps!

Matt.


+-------------------------------------------------------------------+
|Filename: problem.JPG                                              |
|Download: http://www.excelforum.com/attachment.php?postid=4193     |
+-------------------------------------------------------------------+

-- 
mahou
------------------------------------------------------------------------
mahou's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30236
View this thread: http://www.excelforum.com/showthread.php?threadid=499106

0
1/8/2006 2:30:15 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
645 Views

Similar Articles

[PageSpeed] 7

Hi!

Try this (normally entered, not an array):

=SUMPRODUCT(--(ISNUMBER(DATA!A2:A7)),--(MONTH(DATA!A2:A7)=1))

Biff

"mahou" <mahou.21ao8a_1136687701.3708@excelforum-nospam.com> wrote in 
message news:mahou.21ao8a_1136687701.3708@excelforum-nospam.com...
>
> When i use the MONTH(A3) Function and A3 is blank, excel takes it as a
> 0! ie. MONTH(0) and returns January as the month because the date in
> serial is 00-Jan-1900. How do i get around this! Here is my Formula:
>
> I am trying to count the number of months that are used, i have done
> this with this formula below: It works perfect for all months except
> for Jan! because it think that blanks cells are a serial date 0.
>
> {=SUM(IF(MONTH(DATA!A2:A7)=1,1,0))}
>
> __________________
> |   A                    |
> 1 | Date                  |
> 2 | 01-01-05            |
> 3 | 04-04-05            |
> 4 | 04-04-05            |
> 5 |                         |
> 6 | 01-01-05            |
> 7 |                         |
> ----------------------
>
> * T h e   a b o v e   f o r m u l a   a n d   d a t a   r e t u r n s
> J a n   C o u n t   =   4 ,   b u t   a s   y o u   c a n   s e e   t h
> e r e   i s   o n l y   2   J a n u a r y ! *
> : m a d : : c o n f u s e d :
> P L E A S E   H E L P !   L o l
>
> I have tried to use the ISBLANK function but i could not work out how
> to incorporate it into my problem.
>
> I did Ctrl - Shift - Enter to do an array also as you can see by
> bracets..
>
> I also Attach a pic of my problem~ Hope it helps!
>
> Matt.
>
>
> +-------------------------------------------------------------------+
> |Filename: problem.JPG                                              |
> |Download: http://www.excelforum.com/attachment.php?postid=4193     |
> +-------------------------------------------------------------------+
>
> -- 
> mahou
> ------------------------------------------------------------------------
> mahou's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=30236
> View this thread: http://www.excelforum.com/showthread.php?threadid=499106
> 


0
biffinpitt (3172)
1/8/2006 2:49:23 AM
On Sat, 7 Jan 2006 20:30:15 -0600, mahou
<mahou.21ao8a_1136687701.3708@excelforum-nospam.com> wrote:

>
>When i use the MONTH(A3) Function and A3 is blank, excel takes it as a
>0! ie. MONTH(0) and returns January as the month because the date in
>serial is 00-Jan-1900. How do i get around this! Here is my Formula:
>
>I am trying to count the number of months that are used, i have done
>this with this formula below: It works perfect for all months except
>for Jan! because it think that blanks cells are a serial date 0.
>
>{=SUM(IF(MONTH(DATA!A2:A7)=1,1,0))}

=SUMPRODUCT(ISNUMBER(Data!A2:A7)*(MONTH(Data!A2:A7=1)))

Just enter as a normal formula.







>
>__________________
>|   A                    |
>1 | Date                  |
>2 | 01-01-05            |
>3 | 04-04-05            |
>4 | 04-04-05            |
>5 |                         |
>6 | 01-01-05            |
>7 |                         |
>----------------------
>
>* T h e   a b o v e   f o r m u l a   a n d   d a t a   r e t u r n s 
>J a n   C o u n t   =   4 ,   b u t   a s   y o u   c a n   s e e   t h
>e r e   i s   o n l y   2   J a n u a r y ! *    
>: m a d : : c o n f u s e d :    
>P L E A S E   H E L P !   L o l 
>
>I have tried to use the ISBLANK function but i could not work out how
>to incorporate it into my problem.
>
>I did Ctrl - Shift - Enter to do an array also as you can see by
>bracets..
>
>I also Attach a pic of my problem~ Hope it helps!
>
>Matt.
>
>
>+-------------------------------------------------------------------+
>|Filename: problem.JPG                                              |
>|Download: http://www.excelforum.com/attachment.php?postid=4193     |
>+-------------------------------------------------------------------+

--ron
0
ronrosenfeld (3122)
1/8/2006 3:54:12 AM
Typo Alert:

My response should read:

=SUMPRODUCT(ISNUMBER(Data!A2:A7)*(MONTH(Data!A2:A7)=1))




On Sat, 7 Jan 2006 20:30:15 -0600, mahou
<mahou.21ao8a_1136687701.3708@excelforum-nospam.com> wrote:

>
>When i use the MONTH(A3) Function and A3 is blank, excel takes it as a
>0! ie. MONTH(0) and returns January as the month because the date in
>serial is 00-Jan-1900. How do i get around this! Here is my Formula:
>
>I am trying to count the number of months that are used, i have done
>this with this formula below: It works perfect for all months except
>for Jan! because it think that blanks cells are a serial date 0.
>
>{=SUM(IF(MONTH(DATA!A2:A7)=1,1,0))}
>
>__________________
>|   A                    |
>1 | Date                  |
>2 | 01-01-05            |
>3 | 04-04-05            |
>4 | 04-04-05            |
>5 |                         |
>6 | 01-01-05            |
>7 |                         |
>----------------------
>
>* T h e   a b o v e   f o r m u l a   a n d   d a t a   r e t u r n s 
>J a n   C o u n t   =   4 ,   b u t   a s   y o u   c a n   s e e   t h
>e r e   i s   o n l y   2   J a n u a r y ! *    
>: m a d : : c o n f u s e d :    
>P L E A S E   H E L P !   L o l 
>
>I have tried to use the ISBLANK function but i could not work out how
>to incorporate it into my problem.
>
>I did Ctrl - Shift - Enter to do an array also as you can see by
>bracets..
>
>I also Attach a pic of my problem~ Hope it helps!
>
>Matt.
>
>
>+-------------------------------------------------------------------+
>|Filename: problem.JPG                                              |
>|Download: http://www.excelforum.com/attachment.php?postid=4193     |
>+-------------------------------------------------------------------+

--ron
0
ronrosenfeld (3122)
1/8/2006 4:03:57 AM
Hi Biff you are a fricken Legend! Ron you could be too but i used Biff's
Because it was first and it worked perfect thanks heaps!!

MAtt.


-- 
mahou
------------------------------------------------------------------------
mahou's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30236
View this thread: http://www.excelforum.com/showthread.php?threadid=499106

0
1/8/2006 11:22:15 AM
=SUM(IF(AND(Data!A2:A7)<>"",MONTH(Data!A2:A7)=1),1,0)

This works for me.

0
mlincoln (86)
1/9/2006 2:18:42 AM
=SUM(IF(AND(A2:A7)<>"",MONTH(A2:A7)=1),1,0)

This works for me.  I don't get the curly brackets in Excel 2002,
though.

0
mlincoln (86)
1/9/2006 2:46:05 AM
Reply:

Similar Artilces:

Finding merged cells #2
Sorry for posting 2 messages with ref to the same problem but I am in dire need of a solution... I seem to have a problem due to cells in a worksheet being merged, how can i identify these cells? Many thanks, Rick Excel version? If you have 2002 or 2003 you can use Edit, Find, Format (and specify merged cells), Find All. -- Jim Rech Excel MVP "R D S" <ricknewsgroup@members.v21.co.uk> wrote in message news:385u2aF5kslgkU1@individual.net... | Sorry for posting 2 messages with ref to the same problem but I am in dire | need of a solution... | | I seem to have a problem d...

BUG? HQ Items Reports returning duplicate items
The HQ Items Master Quantity List and Items Master Price List reports are returning incorrect results. If an item is included in an assembly, that item is listed twice. If an item is included in seven assemblies, that item is listed eight times. I presume that the report is listing the item once and then repeating the entry for each assembly the item is part of. Each entry counts the full inventory, so your totals for cost, etc. are way off. Ideally, assembly items would not appear at all in either of these reports. At worst, a line item for the assembly ILC might appear but with a...

How to remove blank cells
I have a spreadsheet with A LOT of blank cells. Is there any type of formula I can use to automatically delete them? Hi, Select the entire range and press F5, Special, Blanks, OK. The press Ctrl+- (Control Minus) and choose shift cells... -- If this helps, please click the Yes button. Cheers, Shane Devenshire "DestinySky" wrote: > I have a spreadsheet with A LOT of blank cells. Is there any type of formula > I can use to automatically delete them? On Jun 24, 12:44=A0am, Shane Devenshire <ShaneDevensh...@discussions.microsoft.com> wrote: > Hi, > > Sel...

How do I sum YTD totals based on monthly totals
I have a financial worksheet with expenses by month and a monthly total for each month. At the end of each subsequent month there is a cumulative total of the monthly totals (YTD), I cannot figure out how to get an automatic formula to populate the correct cells to mimick the previous formulas with the current cell data to get the correct totals. -- Donna EMU Alumni "Bsgrad02" <Bsgrad02@discussions.microsoft.com> wrote in message news:8E3201DF-9B59-4188-8E19-BCC24B00012C@microsoft.com... > I have a financial worksheet with expenses by month and a monthly total for &g...

inserting rows using visual basic
I have a data set that I have been working on for the past few days. am new to visual basic and could use a little help with my problem. The data is organized by time (0,1,2,3...22,23 hrs) but not every hou is present. I would like to insert any missing hours WITHOU disrupting the present data. And, any row that is missing an hour, th rest of the cells in that row need to read "0". There is an attached file that shows what I have and also what I need. Thank Attachment filename: missingrows.xls Download attachment: http://www.excelforum.com/attachme...

Date functions
Our database represents dates by giving the day number within a year. For example, day number 1 is January 1, and day number 365 is December 31. Is there a function to convert day number to mmm-dd? =TEXT(DATE(2005,1,A1),"mmm-dd") where cell A1 holds the day of the year "Kirk P." <KirkP@discussions.microsoft.com> wrote in message news:847D34F3-CA15-4BA2-A5EB-578DBBB42E6A@microsoft.com... > Our database represents dates by giving the day number within a year. For > example, day number 1 is January 1, and day number 365 is December 31. Is > there a fun...

Installing and using Office: MAC 2008 Home and Student Addition
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) My husband recently purchased a new MacBook Pro. He gave me his MacBook Air. Office Home and Student addition was installed on his old computer (MacBook Air), and was loaded on the new computer. I purchased my own copy of Office: MAC 2008 Home and Student and installed it on the MacBook Air but can't use when my husband is using his version. The product keys are obviously different, but when he's writing in Word, I cannot. The message is that two people can not use the program at once who are on the same network. What do I do so ...

Cells range into an array, then into msgbox
Hi all, I have a range of cells which are either blank or contain data. I'd like to create a dynamic(?) array and enter the values of those cells. Then, after that, i'd like to output the array into a msgbox. I've started with the below code, but am struggling to get it to work and to do what i'm required. Can anyone help? Cheers, Tony Z. Sub collate() Dim N As Long Arr = Range("F2:H50").Value For N = LBound(Arr) To UBound(Arr) impe = impe + Arr(N) Next N MsgBox impe End Sub Hi Tony Are the values numerics or text Sub co...

Cell text color change
I'm trying to get the text color in a cell to change (to red) if the formula in that cell produces a value above a certain ammount... Could this be done? Look at Format>Conditional Formatting -- HTH RP (remove nothere from the email address if mailing direct) "Steffen" <Steffen@discussions.microsoft.com> wrote in message news:BC53FC5B-81DD-4872-BDCF-A56B42C24159@microsoft.com... > I'm trying to get the text color in a cell to change (to red) if the formula > in that cell produces a value above a certain ammount... > > Could this be done? ...

Cell flashing
Does anyone know how to format a cell so it will flash or blink ? It's possible to do so with code but its a bad idea, you'll find such (bad) code by searching Google. There is no built in function like Word in Excel and if you do use such code the results are usually jumpy and erratic, plus the processor is unavailable to do anything else until the code is stopped from running, ie the machine is virtually locked up, Regards, Alan. "Skioregon" <Skioregon@discussions.microsoft.com> wrote in message news:AD31EA15-BC24-4074-85DA-D826B871EE55@microsoft.com... > D...

adding 150% to a cell
I have created a payroll which i need to create and i've got most set up. i need to know how i can take D2 times it by 150% from E2 and give me the result to G2. Ok to say it simpler Overtime hours is in D2. Normal hourly rate is in E2 and overtime earned is in G2 overtime payment is at the rate of 150% of the normal hourly rate. How can i create such a formula to give the results in G2? At the moment i've got it like this: =SUMPRODUCT(D2)*(E2*150%) which worked fine but i was thinking if this is overkill in how to do it? "Asta" wrote: > I have created a payroll wh...

How can I have an initial zero in an Excel cell?
I want to be able to enter times as eg 0845 but no matter what cell format I use, there is a problem, either that the initial zero is lopped off or that I am informed there is an error. Is there any way of solving this? try <format><cell><number><custom> enter 0000 and you will get a four digit display with an initial display with a leading zero. Note this is not a time function "ALupin" wrote: > I want to be able to enter times as eg 0845 but no matter what cell format I > use, there is a problem, either that the initial zero is lopped off or...

how to find used region with macro
Hi, sorry if this obvious or has been asked recently. I have a macro that goes through all rows and all columns in a selection. If a row or a column is hidden, the font of that row or column is changed. A friend wants to use the macro in a Workbook with many sheets. He asked if I could change it so that he does not need to do a selection on every sheet before he runs it. I imagine that a nested loop through all rows and all columns will take a long time? I�ve tried to figure out how to get the range defined by the last row and the last column filled with data. First I tried with Current...

Linked cells, make the data go hard manually
I'd like to convert a cell linked to a previous day to hard numbers once a day. Is that possible? By 'hard number' you mean one that is no longer linked, you could copy, then paste as, paste value. HTH, Carole O "Johnny" wrote: > I'd like to convert a cell linked to a previous day to hard numbers once a > day. Is that possible? ...

Need help with using a bar graph in a report
I have a report that separates information about representatives by manager. I need the graphs to give a quick summary on a few stats from each rep but only show for that manager's team and not all information. For example: Manager A has RepA, RepB, and RepC under him. Each rep has Aux1, Aux2, Aux3 information which needs to be displayed. My goal is to show Manager A with his Reps A,B, and C of their Aux1, 2, and 3. Then, under the next manager Header I want Manager B to show Rep D, E, and F with their Aux1, 2, and 3 information. Etc thru Manager G. Please help... Th...

Can't open database window using shift key...
Hi there, I have an Access database that I distribute to my client on CDROM. The start up options of the database are set so that the database window is not visible to the users. When the database is updgraded the database manager copies it from the CDROM to the relavant PC and then uses the shift key on opening in order to display the database window and link to the tables in the existing back-end. This procedure has been done many many times without a problem... BUT last week he found that holding down the shift key whilst opening the database no longer opened the database window and leave...

Prevent functions from changing when new rows are added
I have a worksheet configured as a timesheet, where each row represents a task performed. For any given day, I track the task(s) performed and the hours spent on each task, and there are many functions in the worksheet. The worksheet contains the following columns: Column A: blank column (not used for anything). Column B: series of dates (with a blank row in between each date). Column C: tasks performed, manually entered. Column D: hours worked on the task. Column E: hours worked for the week. There's a function in each cell where, if it's Sunday, a sum of the week's...

deleting particular stuff from cells
i need to delete the last four digits of zip codes for about 15000 addresses... Is there anyway to do that all at once... zip exp: 39180-3454 i want to delete the -3454.... i need to do this for about 15,000 zips... can i do it automaticall without doing each cell one by one..... ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ for each c in [b2:b15001]'selection c.value=left(c,len(c)-4) next "billrebels4" <billrebels4.vt6hn@excelforum-nos...

Paste Cells in Column A to Columns B-K
Hi All, I was wondering if someone can help with writing an Excel Macro or some scripting. What I am trying to achieve is the following. In Column A (A1:A1000) there are values of L0xxxxxx xxxxxx=numbers (i.e L0123456) What I want to be able to do is move the values from column A to Columns B-K (10 Columns) 10 values at a time, upto 100 lines. A B C D E F G H I J K L00001 L00001 L00002 L00003 L00004 .5 .6 .7 .8 .9 L00010 L00002 L00011 L00003 L00004 ..5 ..6 ..7 ..8 ..9 L00010 L00011 Any help would be appreciated. T...

Parameter Query using date ranges
I am creating a parameter query where I want to pull data between 2 dates. In the date criteria of my query, if I write between #01/01/2008# and #01/31/2008# +1, I will get all data for January. If I use a form to enter the dates and have Between [Forms]![autoexec]![start] And [Forms]![autoexec]![end] +1 in the date criteria, I get an error. Does anyone know what I should put in the criteria to make the query work? Are you entering your dates as m/d/y? What happens if you try: DateAdd("d", 1, [Forms]![autoexec]![end]) Is the form open? -- Duane Hookom Microsoft Access...

Highlighted cells
I need serious help please. Im using Excel 2000. When Im in Excel, I click on a cell and release the mouse button and all the cells are highlighted. I drag the mouse with no mouse button pressed and it still hightlightes the cell. I cant even close out excel after that. I click on the X and also go to file and it wont process the command. Ive tried rebooting the pc, reloading excel, and even reloading the entire pc. But the problem still exsits. Is there any one who can help with this problem??????? Stephen, First thing to try would be pressing the F8 key. If that doesn'...

Suggestion for using CStringArray with Unicode, mulit-byte, ATL &
Hi, I couldn't find the "development" group so i chose this one. I am trying to bring some code that has the CStringArray in it from a Multi-Byte project into a Unicode project and I keep getting errors I cannot resolve. Scenario: Project type: DLL Use MCF: Shared DLL Use ATL: Not using ATL Min CRT Use in ATL: No Char set: Unicode added afxcoll.h to stdafx.h as suggested elsewhere #include <afxcoll.h> #include "resource.h" #include <atlbase.h> #include <atlcom.h> I get the following error; Error 247 error LNK2005: _DllMain@12 already defined in Te...

Last value in column before blank
Hi, My data in one column A:A - Where V100 &V101 are product codes, dates run down the column and Blank is a blank cell befor each new product code. V100 1-Sep-09 2-Sep-09 Blank V101 1-Oct-09 5-Oct-09 10-Oct-09 Blank Question is how do i return the date value for the last entry (eg before the blank) for each product code I want to find. Note also the last date entry is always the max value date. Eg V100 = 2-Sep and V101 =10-Oct. Thks Craig One formulas play ... Assume your source data running in A2 down Put these in B2:D2 In B2: =IF(LEFT(A2)="V",A2,IF(A2=&...

Empty cells showing that they are default formated to "Date"
I created a spead sheet with one column set as a date the first to the end of the month. The other columns have various in text and accounting info. If I click on a empty cell it show in the format section on the bar the the cell is set up to have a date entry. This is all over the page. Can any one tell me why this is happening? -- Fred You can correct that by setting the formating for the other columns. Simply click on the column(s) then right click and select Format cells, or select the column(s) then go to Format and then Cells. In the selection list choose any option bes...

Combining IF and Count functions
I am a novice with excel and I need assistance creating a formula that will count the number of people that appear by date. How can I ask excel to complete the following calculation: If the date is "10/11/05" count each occurrence of "John"? Here is my data sample: Completion Date Contact Type Person 10-11-2005 Letter John 10-12-2005 Mail Paul 10-11-2005 Mail Richard 10-11-2005 Letter Paul 10-12-2005 Letter Paul 10-12-2005 Letter Paul 10-12-2005 Letter Paul 10-12-2005 Mail Richard 10-12-2005 Letter Paul 10-11-2005 Mail Richard 10-12-2005 Mail Jim 10-12-2005 Letter Pa...