Date or N/A in a cell

Options
LizTo
LizTo ✭✭✭✭✭
edited 12/12/22 in Formulas and Functions

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


or

=[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!

Tags:

Best Answers

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    edited 12/12/22 Answer ✓
    Options

    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
    Ray Lindstrom ✭✭✭✭✭✭
    Answer ✓
    Options

    @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")

Answers

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    edited 12/12/22 Answer ✓
    Options

    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
    LizTo ✭✭✭✭✭
    Options

    @Ray Lindstrom


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

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    Answer ✓
    Options

    @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")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!