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

Justin Mauzy

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?

Thanks in advance.

Paul Newcome

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)))

Paul Newcome

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)

A C

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?

Paul Newcome

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)))

Justin Mauzy

@Paul Newcome Thank you, this worked perfectly!

Paul Newcome

Happy to help. 👍️