# using date boundaries

✭✭✭✭

Hello SS community,

I'm having trouble figuring out the right formula for having SS return the amount of days scheduled within a two dates. I need it to provide only positive numbers and zero for negative values. My columns are: Start date| Finish Date| Duration| Goal period start date| Goal Period end date| Goal Duration|. The formula would be in the Goal Duration column calculating the number of days from the Start Date/End Date columns that fall within the Goal Period Start Date and Goal Period End Date.

Thanks in advance for any help on this.

Thanks,

Kyle

• ✭✭✭✭✭✭

For working days you would use something like this...

=NETWORKDAYS(MAX([Start Date]206, \$[Date to be received]\$403), MIN([Finish date]206, [Goal Period End date]206))

• ✭✭✭✭✭✭

Hi @kyle50541

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

PMP Certified

[email protected]

www.mobilproject.it

• ✭✭✭✭✭✭

This should get you started. It is not the most efficient solution, but it should work.

=IF(OR([Start Date]@row > [Goal Period End Date]@row, [Finish Date]@row < [Goal Period Start Date]@row), 0, IF(AND([Start Date]@row >= [Goal Period Start Date]@row, [Finish Date]@row >= [Goal Period End Date]@row), [Goal Period End Date]@row - [Start Date]@row, IF(AND([Start Date]@row >= [Goal Period Start Date]@row, [Finish Date]@row <= [Goal Period End Date]@row), [Finish Date]@row - [Start Date]@row, IF(AND([Start Date]@row <= [Goal Period Start Date]@row, [Finish Date]@row <= [Goal Period End Date]@row), [Finish Date]@row - [Goal Period Start Date]@row, IF(AND([Start Date]@row <= [Goal Period Start Date]@row, [Finish Date]@row >= [Goal Period End Date]@row), [Goal Period End Date]@row - [Goal Period Start Date]@row)))))

I am going to keep on working on a more efficient solution though, but at least this way you don't have to wait while I test things out to at least get your sheet working.

• ✭✭✭✭✭✭

A little more condensed:

=IF(OR([Start Date]@row > [Goal Period End Date]@row, [Finish Date]@row < [Goal Period Start Date]@row), 0, IF([Start Date]@row >= [Goal Period Start Date]@row, IF([Finish Date]@row >= [Goal Period End Date]@row, [Goal Period End Date]@row - [Start Date]@row, [Finish Date]@row - [Start Date]@row), IF([Finish Date]@row >= [Goal Period End Date]@row, [Goal Period End Date]@row - [Goal Period Start Date]@row, [Finish Date]@row - [Goal Period Start Date]@row)))

• ✭✭✭✭✭✭

And here is the shortest/most efficient I personally could come up with:

=MAX(0, MIN([Finish Date]@row, [Goal Period End Date]@row) - MAX([Start Date]@row, [Goal Period Start Date]@row))

• ✭✭✭✭

Paul,

Thanks for the effort! I think it's pretty much working but I also need it to calculate work days only or "networkdays" How would I augment your function to add this feature?

=MAX(0, MIN([Finish date]206, [Goal Period End date]206) - MAX([Start Date]206, \$[Date to be received]\$403))

The function I'm using is above. I substituted a cell reference instead of using the "goal start period start date" column.

As you can see I'm getting 3.7... for goal duration. Not sure why that would be a decimal.

Thanks,

Kyle

• ✭✭✭✭✭✭

For working days you would use something like this...

=NETWORKDAYS(MAX([Start Date]206, \$[Date to be received]\$403), MIN([Finish date]206, [Goal Period End date]206))

• ✭✭✭✭

Thanks Paul! It works! Appreciate you!

Kyle

• ✭✭✭✭✭✭

Happy to help. 👍️

• ✭✭✭✭

One more thing Paul. Where there is no date entered in "start date" column the formula is returning all working days from \$[Date to be received]\$403 to [Goal Period End date]206. Can you adjust the formula to make those instances zero?

thanks again

Kyle

• ✭✭✭✭✭✭

Sure thing...

=IF([Start Date]@row <> "", NETWORKDAYS(MAX([Start Date]206, \$[Date to be received]\$403), MIN([Finish date]206, [Goal Period End date]206)), 0)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!