Calculate the number of days between two dates if date cell is blank

Options
Shell Strozyk
edited 07/13/20 in Smartsheet Basics

Hi There,


I am trying to insert a formula to calculate the number of days between two dates, which I have.... Created Date and Accepted Date. My question is,

How can I add to the formula If there is no date entered into "Accepted Date", to then calculate the number of days between "Created Date" and today's date?


Thanks in advance. :-)

Answers

  • Stefan
    Stefan ✭✭✭✭✭✭
    Options

    Hi,

    try this

    =IF(ISBLANK([Accepted Date]@row), NETWORKDAYS([Created Date]@row, TODAY()), NETWORKDAYS([Created Date]@row, [Accepted Date]@row))

    Hope this helps

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Another approach would be to nest the IF statement inside of the NETWORKDAYS function (or whatever function/formula you are using).

    =NETWORKDAYS([Created Date]@row, IF([Created Date]@row <> "", [Created Date]@row, TODAY()))


    =[Created Date]@row - IF([Created Date]@row <> "", [Created Date]@row, TODAY())

  • Nicole Luter
    edited 08/26/20
    Options

    I have a similar need - can someone help? Looking for Content Age (in months) column on each row...

    Example --

    ROW 1:

    Posting Date Verified= 2/12/20

    Posting Status= Red

    EOL Date= 8/10/20


    ROW 2:

    Posting Date Verified= 3/12/20

    Posting Status= Green

    EOL Date= (blank)


    I need a formula for the Content Age (in months) column, calculating this:

    If EOL Date is not Blank, then Content Age should say “EOL”

    If EOL Date IS Blank, then Content Age should say the calculation of the number of months (rounded?) since the Posting Date Verified date (to today).

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @Nicole Luter Rounded up or down? Using the examples above, what would be your desired outcome?