If Statements that return blank values

  • Thread starter Thread starter Mark
  • Start date Start date
I have tried the "" and when I date format the cell it
returns 0-Jan-00. This leads me to believe that the cell
is populated. Only when I clear the contents of the cell
do I received my desired result of a blank cell.

Do I need to do anything else?

Mark
 
Hi Mark

It also work with a formula cell with a date format

Show us your formula
 
Ron,

The return value is being drawn into a pivot table. It is
in the pivot table where the blank value when date
formated returns the 0-Jan-00 value.

The formula is as follows:

IF(J3497=102,IF(I3497>0,(I3497+365),""),"")

Mark
 
Hi Mark

I test that tomorrow for you if you don't get a answer
I don't use Pivot's much
 
Hi Mark!

Your formula works OK for me if I3497 is an empty cell.

I get #VALUE! with a space in the cell or other (more obvious) text. I
also get #VALUE! if I3497 contains a formula.

But what is your formula doing?

If adding a year use:
=IF(J3497=102,IF(I3497>0,DATE(YEAR(I3497)+1,MONTH(I3497),DAY(I3497)),"
"),"")

If finding the day before the expiry of a year:
=IF(J3497=102,IF(I3497>0,DATE(YEAR(I3497)+1,MONTH(I3497),DAY(I3497)-1)
,""),"")

By just adding 365 you are exposing yourself to different results for
Leap Years.

But I prefer the standard approach:
=IF(COUNTA(I3497:J3497)<2,"",IF(J3497=102,DATE(YEAR(I3497)+1,MONTH(I34
97),DAY(I3497)-1)))
Or perhaps covering either having a blank cell or formula returning ""
=IF(OR(I3497="",J3497=""),"",IF(J3497=102,DATE(YEAR(I3497)+1,MONTH(I34
97),DAY(I3497)-1),""))


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top