Calculate lead time as a positive value if not blank

Any thoughts?

=ABS(SUMIF(Timestamp@row, <>"") - [Final Project Due Date]@row, <>"")


The following provides a positive value:

=ABS(SUM((Timestamp@row) - [Final Project Due Date]@row))

I need to find a way to exclude rows where dates were not entered so the column can be averaged.

Best Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Darrin Kiessling

    Instead of using a SUMIF, try using an IF statement with the SUM inside of it, like so:

    =IF(OR(Timestamp@row = "", [Final Project Due Date]@row = ""), "", ABS(SUM(Timestamp@row - [Final Project Due Date]@row)))


    This way you can say that if the Timestamp or the Due Date are blank, return blank, otherwise do your calculation. Let me know if this is what you were looking to do!

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Darrin Kiessling

    Very close! You could either have a second IF statement instead of using AND, or you could add the statement as another OR option:

    =IF(OR(Timestamp@row = "", [Final Project Due Date]@row = "", Timestamp@row > DATE(2021, 9, 28)), "", ABS(SUM(Timestamp@row - [Final Project Due Date]@row)))


    The OR is essentially just stopping us from needing to do this, writing the IF 3 times:

    =IF(Timestamp@row = "", "",

    IF([Final Project Due Date]@row = "", "",

    IF(Timestamp@row > DATE(2021, 9, 28)), "",

    ABS(SUM(Timestamp@row - [Final Project Due Date]@row))

    )))

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!