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?
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" <[email protected]> wrote in message
news:[email protected]...
> 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
gary_brown@ge_NOSPAM.com
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
> gary_brown@ge_NOSPAM.com
> 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
gary_brown@ge_NOSPAM.com
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
> > gary_brown@ge_NOSPAM.com
> > 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
> gary_brown@ge_NOSPAM.com
> 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
> > > gary_brown@ge_NOSPAM.com
> > > 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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks