using date boundaries

Kyle Keever

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

Paul Newcome

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

Bassam Khalil

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.

Paul Newcome

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.

Paul Newcome

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

Paul Newcome

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

Kyle Keever

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

Paul Newcome

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

Kyle Keever

Thanks Paul! It works! Appreciate you!

Kyle

Paul Newcome

Happy to help. 👍️

Kyle Keever

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

Paul Newcome

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)