Date and If problem

  • Thread starter Thread starter Bob
  • Start date Start date
B

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
 
Hi Bob,

Am Thu, 21 Jun 2012 12:46:58 -0700 (PDT) schrieb Bob:
I am using in B2

=IF(Report!H6 =" ";"";Report!H6)
Click to expand...

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
 
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
Click to expand...

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
 
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
Click to expand...

good point. I agree.


Regards
Claus Busch
 
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
 
Back
Top