Formula help to convert from total days to business days

jgenove

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?

=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

=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

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

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.