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

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]))
Best Answer
-
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)
Answers
-
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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 207 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!