How do I calculate the count of project days within a quarter given the project start and end dates?

mlj

I have a sheet with all of the current projects, each project is along one row. I'm looking to create a column formula that is a count of project days if the project falls within a specific quarter. I've tried to type this out but cannot get the syntax right in smartsheets. I was thinking it would be one IFS statement with four main components. Start Date and End Date column types are date, pulled in/linked from another sheet.

Visual for each of the components:

<-----------QStart-------------------------------QEnd--->

<---1Start------------------1End--->

<---2Start-------------------------------------------------------2End--->

<-----------------------3Start----------------------------------3End--->

<-----------------------4Start----------4End--->

Actual Formula entered in SmartSheets, returned Unparseable:

IFS((IF(AND((DATE(2023, 1, 1) >= [Start Date]@row),([End Date]@row <= DATE(2023, 3, 31))), NETWORKDAYS(DATE(2023, 1, 1),[End Date]@row))),(IF(AND((DATE(2023, 1, 1) >= [Start Date]@row),([End Date]@row >= DATE(2023, 3, 31))), NETWORKDAYS(DATE(2023, 1, 1),DATE(2023, 3, 31)))),(IF(AND(([Start Date]@row >= DATE(2023, 1, 1)),([End Date]@row >= DATE(2023, 3, 31))), NETWORKDAYS([Start Date]@row,DATE(2023, 3, 31)))),IF(AND((DATE(2023, 1, 1) >= [Start Date]@row),(DATE(2023, 3, 31) >= [End Date]@row)), NETWORKDAYS([Start Date]@row,[End Date]@row)))

Breakdown for each component:

// 1

// if the project starts before the quarter starts

// and the project ends before the quarter ends

// then count the number of days from the quarter start date to the project end date

IF [quarter start date] is greater than [project start date]

AND [project end date] is less than [quarter end date]

THEN NETWORKDAYS(quarter start date], [project end date])

IF(AND(([QStart] >= [PStart]),([PEnd] <= [QEnd])), NETWORKDAYS([QStart],[PEnd]))

// 2

// if the project starts before the quarter starts

// and the project ends after the quarter ends

// then count the number of days in the quarter

IF [quarter start date] is greater than [project start date]

AND [project end date] is greater than [quarter end date]

THEN NETWORKDAYS([quarter start date],[quarter end date])

IF(AND(([QStart] >= [PStart]),([PEnd] >= [QEnd])), NETWORKDAYS([QStart],[QEnd]))

//3

// if the project starts after the quarter starts

// and the project ends after the quarter ends

// then calculate the network days between the project start and the quarter end 

IF [project start date] is greater than [quarter start date]

AND [project end date] is greater than [quarter end date]

THEN NETWORKDAYS([project start date],[quarter end date])

IF(AND(([PStart] >= [QStart]),([PEnd] >= [QEnd])), NETWORKDAYS([PStart],[QEnd]))

// 4

// if the project starts after the quarter starts

// and the project ends before the quarter ends

// then count the project days

IF [project start date] is greater than [quarter start date]

AND [quarter end date] is greater than [project start date]

THEN NETWORKDAYS([project start date], [project end date])

IF(AND(([QStart] >= [PStart]),([QEnd] >= [PEnd])), NETWORKDAYS([PStart],[PEnd]))

Paul Newcome

There is no IFS function in Smartsheet. Try this instead.

=MAX(NETWORKDAYS(MAX([Start Date]@row, DATE(2023, 01, 01)), MIN([End Date]@row, DATE(2023, 03, 31))), 0)

Paul Newcome

There is no IFS function in Smartsheet. Try this instead.

=MAX(NETWORKDAYS(MAX([Start Date]@row, DATE(2023, 01, 01)), MIN([End Date]@row, DATE(2023, 03, 31))), 0)