Masking a cell's default value and formula

eganji

In the sheet below columns C, E, and K are calculated based on values entered in columns H and I. The formulas are copied in rows throughout the sheet and return a default value of 01/01/40 before they are triggered. How can I mask/hide the default value before values are entered?

The desired outcome should be as the highlighted rows below with the rest of the sheet blank (no more 01/01/40 while holding the formulas) until values are entered into columns H and I.

eganji

I am working on this to include the other required factors and I think it is going to work out. This made a big difference for me and thank you very much.

David Tutwiler

If you're looking to not return anything until a date is entered, I would suggest you put a null value into your formula that is calculating these in the False area. In an example formula it would look like:

=IF(H@row "", {some formula stuff}, "")

Where {some formula stuff} is your formula for calculating the dates if a date exists and "" is the result if no date exists

You may have to uncheck a box in the column properties that says "Restrict values to only dates".

eganji

Thank you, David.

There is always a chance that columns H and I don’t have values in which case columns C, E, and K would return a #INVALID DATA which will in turn return errors in other formulas and cells. To prevent that I have added a reference date and the condition below:

IF(OR(ISBLANK([H]@row), ISBLANK([I]@row)), DATE(YEAR($[Reference Date]$1), MONTH($[Reference Date]$1), DAY($[Reference Date]$1)) and the rest of the formula starts from here...

I think adding IF(H@row "", {some formula stuff}, "") will negate what I am trying to do or has no effect since I am making sure H@row is always populated so it will never be " ". Or I will need to re-think making sure blank H and I will not return error.

Here is the whole formula:

=IF(OR(ISBLANK([H]@row), ISBLANK([I]@row)), DATE(YEAR($[Reference Date]$1), MONTH($[Reference Date]$1), DAY($[Reference Date]$1)), IF(NETDAYS([H]@row, [I]@row) / 360 > 12, DATE(YEAR([I]@row) + 7, MONTH([I]@row), DAY([I]@row)), DATE(YEAR([H]@row) + 19, MONTH([H]@row), DAY([H]@row)))) 

David Tutwiler

If I understand your scenario correctly, you want to display a blank field instead of a date any time there isn't data in the H or I column, correct?

If that is the case, I think all you need to do is replace the DATE( #Reference Date stuff) with "". This will put a blank statement in the cell instead of any date. It would look like:

=IF(OR(ISBLANK([H]@row), ISBLANK([I]@row)), "", IF(NETDAYS([H]@row, [I]@row) / 360 > 12, DATE(YEAR([I]@row) + 7, MONTH([I]@row), DAY([I]@row)), DATE(YEAR([H]@row) + 19, MONTH([H]@row), DAY([H]@row)))) 

If I'm misunderstanding the problem, I apologize and would ask that you correct my thinking.

eganji

I am working on this to include the other required factors and I think it is going to work out. This made a big difference for me and thank you very much.