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?

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?

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?

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?

=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?

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?

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