Sum of Multiple Date Ranges

Stephanie Stoewe

Hello, 

I am trying to find the sum of Net Work Days between three date ranges. 

Expected Dock Date  &  Dock Date

Available for QC   &   QC Complete

Date Received into Sage  &  Expected Receipt

This formula isn't quite working.  It may be that not every one of these dates has been populated yet, but ideally it would be a + or - number value.

=SUM(([Dock Date]1,[Expected Dock Date]1) + ([Available for QC]1,[QC Complete]1) + ([Expected Sage Receipt Date]1,[Date Received into Sage]1)

 

Thoughts?

 

Mike Wilday

Try this: 

=SUM(Networkdays([Dock Date]1,[Expected Dock Date]1), Networkdays([Available for QC]1, [QC Complete]1), Networkdsays([Expected Sage Receipt Date]1,[Date Received into Sage]1))

Paul Newcome

For the basic formula you would want something like this...

=SUM(NETWORKDAYS([Dock Date]1,[Expected Dock Date]1), NETWORKDAYS([Available for QC]1,[QC Complete]1), NETWORKDAYS([Expected Sage Receipt Date]1,[Date Received into Sage]1))

 

To account for blanks so that your SUM function works properly, you would either want to use an IF statement to populate the date or an IFERROR statement built in.

Stephanie Stoewe

#UNPARSEABLE

Stephanie Stoewe

This works!  But I believe if there is no date, it returns #INVALID.  Would the IFERROR combat that? If so, how would you build that in?  

Paul Newcome

It depends on how you would want the SUM function to work if there is a blank. Would you want it to consider the blank as TODAY, or would you want it to skip over that particular set of dates?