Calculate Working Days with blank field

Jaime Elizabeth

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!

rhj09

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!

rhj09

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!

Jaime Elizabeth

Thank you @rhj09!! I actually typed the wrong column in my original post (*face palm*), but very glad I got everything correct. Thank you!!

rhj09

Glad to know that it was helpful. Cheers! @Jaime Ciabattoni