month worksheet function returns wrong value
using formula =month(a1) and in a1 is a date of 01 01 2006 and it returns
value of 1 which is correct, however if i delete the date in a1 it still returns a value of 1. how can I resolve this? |
month worksheet function returns wrong value
Hi!
You need to test cell A1 and make sure there is a date entered: =IF(ISNUMBER(A1),MONTH(A1),"") Or, maybe something like this: =ISNUMBER(A1)*MONTH(A1) Will return either 0 or the month number Biff "Barry" wrote in message ... using formula =month(a1) and in a1 is a date of 01 01 2006 and it returns value of 1 which is correct, however if i delete the date in a1 it still returns a value of 1. how can I resolve this? |
month worksheet function returns wrong value
Hey, Barry.
A blank cell has a 0 value. A 0 value as a date is Jan-00-1900 so a return of 1 for Month(Jan-00-1900) is correct. HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "Barry" wrote: using formula =month(a1) and in a1 is a date of 01 01 2006 and it returns value of 1 which is correct, however if i delete the date in a1 it still returns a value of 1. how can I resolve this? |
month worksheet function returns wrong value
Gary, could you offer me a solution please.
"Gary L Brown" wrote: Hey, Barry. A blank cell has a 0 value. A 0 value as a date is Jan-00-1900 so a return of 1 for Month(Jan-00-1900) is correct. HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "Barry" wrote: using formula =month(a1) and in a1 is a date of 01 01 2006 and it returns value of 1 which is correct, however if i delete the date in a1 it still returns a value of 1. how can I resolve this? |
month worksheet function returns wrong value
Thanks, but the following worked the best.
=IF(YEAR(A48)=1900,0,IF(MONTH(A48)=MONTH($C$1),1,0 )) "Gary L Brown" wrote: =if(len(a1)=0,"",month(a1)) HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "Barry" wrote: Gary, could you offer me a solution please. "Gary L Brown" wrote: Hey, Barry. A blank cell has a 0 value. A 0 value as a date is Jan-00-1900 so a return of 1 for Month(Jan-00-1900) is correct. HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "Barry" wrote: using formula =month(a1) and in a1 is a date of 01 01 2006 and it returns value of 1 which is correct, however if i delete the date in a1 it still returns a value of 1. how can I resolve this? |
month worksheet function returns wrong value
This formula would do the same =(A48<"")*(MONTH($C$1)=MONTH(A48)) and has the advantage that it also works with 1904 date system -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=505841 |
All times are GMT +1. The time now is 05:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com