Calculate Working Days with blank field

Options

Hi there,

I'm trying to calculate the number of working days on a sheet between 2 date columns ("Date Opened" and "Candidate Start Date"). However, if the "Candidate Start Date" column is blank, I want to calculate the number of working days between Today and "Date Opened".

I was hoping someone could just check my logic and make sure I have this formula correct:
=IF([Candidate Start Date]@row = "", NETWORKDAY([Date Opened]@row, TODAY()), NETWORKDAY([Date Opened]@row, [Candidate Start Date]@row))

Thank you!

Answers

  • rhj09
    Options

    Your formula looks almost correct! However, to handle the case when "Candidate Start Date" is blank, you need to use TODAY() instead of [Candidate Start Date]@row in the second parameter of the IF function. Here's the corrected formula:

    excelCopy code=IF(ISBLANK([Candidate Start Date]@row), NETWORKDAYS([Date Opened]@row, TODAY()), NETWORKDAYS([Date Opened]@row, [Candidate Start Date]@row))
    
    

    This formula will calculate the number of working days between "Date Opened" and "Candidate Start Date" if "Candidate Start Date" is not blank. If "Candidate Start Date" is blank, it will calculate the number of working days between "Date Opened" and today.

    Hope this helps!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!