# Is there a way to calculate Netdays using 2 sets of dates

Options
✭✭✭✭✭

I have 4 columns with dates (they are set as date columns). Here is what I want to do using the following example:

• These are the 4 columns with their values:
• Check-In Date: 01/27/2024
• Check-Out Date: 02/10/2024
• Start Date: 01/01/2024
• End Date: 02/04/2024

• The Netdays between the Check-In Date and Check-Out Date = 15
• But the Netdays of the Check-In Date and Check-Out Date that fall between the Start Date and End Date = 9

Is there a way to calculate the Netdays within a date range this using a formula?

• ✭✭✭✭✭✭
Options

That would look something more like this:

=IF(AND([Check-In Date]@row <> "", [Check-Out Date]@row <> ""), MAX(0, MIN([Check-Out Date]@row, [End Date]@row) - MAX([Check-In Date]@row, [Start Date]@row)))

• ✭✭✭✭✭✭
Options

You would use a MAX function to grab the latest between start and check-in and then a MIN to grab the earliest between the end and check-out. Then you would subtract the max from the min.

=MIN([Check-Out Date]@row, [End Date]@row) - MAX([Check-In Date]@row, [Start Date]@row)

• ✭✭
Options

Thank you for the quick response, Paul. The formula definitely works. However, it is also pulling negative numbers and showing numbers when dates are blank. Here is an example:

With this formula, is there a way to have these shown as blanks or a 0?

• ✭✭✭✭✭✭
Options

That would look something more like this:

=IF(AND([Check-In Date]@row <> "", [Check-Out Date]@row <> ""), MAX(0, MIN([Check-Out Date]@row, [End Date]@row) - MAX([Check-In Date]@row, [Start Date]@row)))

• ✭✭✭✭✭
Options

@Paul Newcome Thank you, this worked perfectly!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!