Masking a cell's default value and formula

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.

Find more posts tagged with

Accepted answers

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.

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.