Formula help to convert from total days to business days

I have this formula which gives me the total # of days to complete a construction interval,

=IF(NOT(ISBLANK([Construction in Progress Date]@row)), ([Into Pending Lateral Date]@row - [Construction in Progress Date]@row))

How can I convert this to business/work days total?

Find more posts tagged with

Conditional Formatting

Accepted answers

=Chris Palmer

How about this:
=IF(AND(NOT(ISBLANK([Construction in Progress Date]@row)), NOT(ISBLANK([Into Pending Lateral Date]@row))),NETWORKDAYS([Construction in Progress Date]@row, [Into Pending Lateral Date]@row),"")

It ensures both start and end dates are present.
Only then computes business days, otherwise, returns a blank (""), not 1 or error

All comments

=Chris Palmer

Hello,
Something that involves NETWORKDAYS perhaps?

=IF(NOT(ISBLANK([Construction in Progress Date]@row)), NETWORKDAYS([Construction in Progress Date]@row, [Into Pending Lateral Date]@row),"")

If you have a column with holidays this will account for those as well:

=IF(NOT(ISBLANK([Construction in Progress Date]@row)),NETWORKDAYS([Construction in Progress Date]@row, [Into Pending Lateral Date]@row,[Holidays]:[Holidays]),"")

jgenove

Thanks @Chris Palmer the formula works when the cell is not blank but for some reason when it is blank it is returning a 1.

=Chris Palmer

It ensures both start and end dates are present.
Only then computes business days, otherwise, returns a blank (""), not 1 or error

jgenove

@Chris Palmer This worked perfectly. I really appreciate your help!

=Chris Palmer

https://community.smartsheet.com/discussion/comment/483586#Comment_483586

Awesome! Happy this worked for you.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.