|
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...o&userid=30236 View this thread: http://www.excelforum.com/showthread...hreadid=499106 |
When using MONTH function on Blank Cell!! Returns Month=Jan!
Hi!
Try this (normally entered, not an array): =SUMPRODUCT(--(ISNUMBER(DATA!A2:A7)),--(MONTH(DATA!A2:A7)=1)) Biff "mahou" wrote in message ... 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...o&userid=30236 View this thread: http://www.excelforum.com/showthread...hreadid=499106 |
When using MONTH function on Blank Cell!! Returns Month=Jan!
On Sat, 7 Jan 2006 20:30:15 -0600, mahou
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 |
When using MONTH function on Blank Cell!! Returns Month=Jan!
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 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 |
When using MONTH function on Blank Cell!! Returns Month=Jan!
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...o&userid=30236 View this thread: http://www.excelforum.com/showthread...hreadid=499106 |
When using MONTH function on Blank Cell!! Returns Month=Jan!
=SUM(IF(AND(Data!A2:A7)<"",MONTH(Data!A2:A7)=1),1 ,0)
This works for me. |
When using MONTH function on Blank Cell!! Returns Month=Jan!
=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. |
| All times are GMT +1. The time now is 10:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com