Sum of Multiple Date Ranges

edited 12/09/19 in Using Smartsheet
12/28/18 Edited 12/09/19

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?

 

Popular Tags:

Comments

  • Mike WildayMike 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 NewcomePaul Newcome ✭✭✭✭✭
    edited 12/28/18

    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.

  • #UNPARSEABLE :(

  • 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 NewcomePaul 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?

Sign In or Register to comment.