Calculate Working Days with blank field

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!

Find more posts tagged with

Sheets

Formulas

Accepted answers

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!

All comments

rhj09

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.