If cell date blank leave formula blank

Hi All, using the current formula to add month to a date. But in some cases the [Sub Rivised]@Row date is blank. instead of throwing an invalid error data i would like the output cell to remain blank. I have tried a few variations with ISBLANK but to no avail. Any suggestions

Formula:

=DATE(IF(MOD(VALUE(MONTH([Sub Revised]@row)) + [Estimated Months for Approval]6, 12) = 0, -1, 0) + YEAR([Sub Revised]@row) + (MONTH([Sub Revised]@row) + [Estimated Months for Approval]6 - MOD(MONTH([Sub Revised]@row) + [Estimated Months for Approval]6, 12)) / 12, IF(MOD(VALUE(MONTH([Sub Revised]@row) + [Estimated Months for Approval]6), 12) = 0, 12, MOD(MONTH([Sub Revised]@row) + [Estimated Months for Approval]6, 12)), DAY([Sub Revised]@row))

Look forward to your input

Mike

Best Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @Mike DeLuca

    I hope you're well and safe!

    Try something like this.

    =IF([Sub Revised]@row="","",DATE(IF(MOD(VALUE(MONTH([Sub Revised]@row)) + [Estimated Months for Approval]6, 12) = 0, -1, 0) + YEAR([Sub Revised]@row) + (MONTH([Sub Revised]@row) + [Estimated Months for Approval]6 - MOD(MONTH([Sub Revised]@row) + [Estimated Months for Approval]6, 12)) / 12, IF(MOD(VALUE(MONTH([Sub Revised]@row) + [Estimated Months for Approval]6), 12) = 0, 12, MOD(MONTH([Sub Revised]@row) + [Estimated Months for Approval]6, 12)), DAY([Sub Revised]@row)

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    @Mike DeLuca

    Excellent!

    Easy to miss!

    You're more than welcome!

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @Mike DeLuca

    I hope you're well and safe!

    Try something like this.

    =IF([Sub Revised]@row="","",DATE(IF(MOD(VALUE(MONTH([Sub Revised]@row)) + [Estimated Months for Approval]6, 12) = 0, -1, 0) + YEAR([Sub Revised]@row) + (MONTH([Sub Revised]@row) + [Estimated Months for Approval]6 - MOD(MONTH([Sub Revised]@row) + [Estimated Months for Approval]6, 12)) / 12, IF(MOD(VALUE(MONTH([Sub Revised]@row) + [Estimated Months for Approval]6), 12) = 0, 12, MOD(MONTH([Sub Revised]@row) + [Estimated Months for Approval]6, 12)), DAY([Sub Revised]@row)

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Mike DeLuca
    Mike DeLuca ✭✭✭✭

    HI Andrée Starå that was a simple fix than i thought it would be. Thanks for taking the time to contribute to the solution.

    Mike

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    @Mike DeLuca

    Excellent!

    Easy to miss!

    You're more than welcome!

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!