Date or N/A in a cell

I am trying to figure out a column formula that will auto-populate a cell with either a date or an N/A:

N/A, if the [TOTAL BUDGET] cell is under $100,000

=[Final Report Due]@row + 15 if the [TOTAL BUDGET] cell@row is $100,000 or more (this would be the audit report due date)

I tried this but it's not quite working:

=IF([TOTAL BUDGET]@row < $100,000, "N/A", IF([TOTAL BUDGET]@row >= $100,000, "[Final Report Due]@row +15"))

One problem seems to be that if I set the Column Type to "Date", then putting N/A isn't possible. But if I set the Column Type to Text/Number, the date calculations don't work.

Is there another way to approach this?

Thank you in advance for your help!

Find more posts tagged with

Formulas

Accepted answers

Ray Lindstrom

Hi @LizTo,

=IF([TOTAL BUDGET]@row < 100000, [Final Report Due]@row + 15, "N/A")

Ensure the column you're calculating in is a Date column, but not restricted to Date values which allows for the "N/A".

All the best,

-Ray

Ray Lindstrom

@LizTo,

Awesome, glad you got it!

You could have also just flipped the < to a and left the results in that order. Either way would accomplish the same.

=IF([TOTAL BUDGET]@row < 100000, [Final Report Due]@row + 15, "N/A")

would become

=IF([TOTAL BUDGET]@row > 100000, [Final Report Due]@row + 15, "N/A")

All comments

Ray Lindstrom

Hi @LizTo,

=IF([TOTAL BUDGET]@row < 100000, [Final Report Due]@row + 15, "N/A")

Ensure the column you're calculating in is a Date column, but not restricted to Date values which allows for the "N/A".

All the best,

-Ray

LizTo

@Ray Lindstrom

Ah, thank you! That works (after switching the value if true and the value if false.

Ray Lindstrom

@LizTo,

Awesome, glad you got it!

You could have also just flipped the < to a and left the results in that order. Either way would accomplish the same.

=IF([TOTAL BUDGET]@row < 100000, [Final Report Due]@row + 15, "N/A")

would become

=IF([TOTAL BUDGET]@row > 100000, [Final Report Due]@row + 15, "N/A")

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.