Date and If problem

[Bob]

I am trying to get a cell (B2) to auto fill if a cell on a 2nd
worksheet (H6) contains a date.

I am using in B2

=IF(Report!H6 =" ";"";Report!H6)

If there is a date in H6 then B2 correctly shows as the date, but if
H6 is blank then B2 shows as
00January1900

I have B2 formatted as date, when i do not then the date shows as
465678.

How can i resolve this problem,

With thanks

Bob

 

[Claus Busch]

Hi Bob,

Am Thu, 21 Jun 2012 12:46:58 -0700 (PDT) schrieb Bob:

I am using in B2

=IF(Report!H6 =" ";"";Report!H6)

there is a typo in your formula. If Report!H6=" " means if Report!H6
contains a space. If Report!H6 is empty you get the value of an empty
cell 0 and that's 00. January 1900.
Try:
=IF(Report!H6="","",Report!H6)


Regards
Claus Busch

 

[GS]

Claus Busch has brought this to us :

Hi Bob,

Am Thu, 21 Jun 2012 12:46:58 -0700 (PDT) schrieb Bob:


there is a typo in your formula. If Report!H6=" " means if Report!H6
contains a space. If Report!H6 is empty you get the value of an empty
cell 0 and that's 00. January 1900.
Try:
=IF(Report!H6="","",Report!H6)


Regards
Claus Busch

To avoid ambiguity about cells being empty or not, I use...

=NOT(Reort!H6)

...which evaluates to FALSE for the range if it's empty, meaning the
return is TRUE for the formula. So...

=IF(NOT(Report!H6),"",Report!H6)

...which translates that
IF Report!H6 is empty THEN
return an empty string
ELSE
return the value in Report!H6


Alternatively, to check if a cell is not empty...

=IF(LEN(Report!H6),Report!H6,"")

...which translates that
IF Report!H6 is NOT empty THEN
return the value in Report!H6
ELSE
return an empty string

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

sorry about the typo:

To avoid ambiguity about cells being empty or not, I use...

=NOT(Report!H6)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Claus Busch]

Hi Garry,

Am Thu, 21 Jun 2012 17:42:22 -0400 schrieb GS:

To avoid ambiguity about cells being empty or not, I use...

=NOT(Reort!H6)

..which evaluates to FALSE for the range if it's empty, meaning the
return is TRUE for the formula. So...

=IF(NOT(Report!H6),"",Report!H6)

..which translates that
IF Report!H6 is empty THEN
return an empty string
ELSE
return the value in Report!H6

Alternatively, to check if a cell is not empty...

=IF(LEN(Report!H6),Report!H6,"")

..which translates that
IF Report!H6 is NOT empty THEN
return the value in Report!H6
ELSE
return an empty string

good point. I agree.


Regards
Claus Busch

 

[Bob]

Hi Garry,

Am Thu, 21 Jun 2012 17:42:22 -0400 schrieb GS:













good point. I agree.

Regards
Claus Busch

Thank you gentleman, help is much appreciated.

Bob

 

[GS]

Claus,
I can't tell you the number of times I found people use the 'Spacebar'
to 'clear contents' from cells. And then they wonder why their calc
formulas suddenly return errors! Hence, I typically change things as
noted to hopefully educate users (OR at the very least persuade them
away from that bad habit).<g>

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion