Calculate number of days between two dates in the same column on the same sheet

Phoenix_FFE

Hello,

Can a combination of the INDEX(COLLECT and NETDAYS Formula be used to get the number of days between two dates in the same column?

I want to get the number of days between two dates on the same sheet, in the same column, that have matching names and different project types.

For Example: Studio Name is equal to Studio Name on a different row (Same Sheet) and has a different project type (Project A / Project B). Then calculate the number of days between the start date of the two rows (Start date #1 - 10/20/25 | Start date #2 - 10/30/25). It shows the net days between the two rows in a helper column.

jmyzk_cloudsmart_jp

Hi @Phoenix_FFE

https://app.smartsheet.com/b/publish?EQBCT=e352d860c0d9405780761d51d2a4e95d

In the demo solution above, I calculated the [Next Same Studio Start] column's date value with the following formula;

[Next Same Studio Start]
=IFERROR(
  INDEX(
    COLLECT(
      Start:Start, 
      Start:Start, >Start@row, 
      [Studio Name]:[Studio Name], [Studio Name]@row, 
      [project type]:[project type], <>[project type]@row
      ), 
    1), 
"")


[Net days to Next]
=IF(ISDATE([Next Same Studio Start]@row), NETWORKDAYS(Start@row, [Next Same Studio Start]@row))

The formula collects the start dates that are later than the start date of the row, and the Studio Name is the same, and the project type is different.

Then, the second formula gets the net days.