Skip to Content

Formula Assistance

I have a process start date column and a process end date column.

I also have dates in six columns - 1st Received Date, 1st Sent Date; 2nd Received Date, 2nd Sent Date; 3rd Received Date, 3rd Sent Date.

I am trying to calculate total days in process - BUT need to exclude the time between received and sent periods(including the actual received and sent dates). In addition, I need to exclude weekends.

For example: Process Start Date 1/1/2016 ; Process End Date 2/29/2016 - 60 Days including 1/1 and 2/29

1st Received Date 1/14/16

1st Sent Date 1/16/16

This would be a Monday, Tuesday, and Wed- would want to subtract 3 days from total process time

2nd Received Date 1/25/16

2nd Sent Date 1/29/16

This would be a Monday, Tuesday, Wed, Thurs, Fri - would want to subtract 5 days from total process time

3rd Received Date 2/15/16

3rd Sent Date 2/17/16

This would be a Monday, Tuesday and Wed - I would want to subtract 3 days from total process time

In addition, all weekends to be excluded.

60 Days - 18 weekend days - 3 days - 5 days - 3 days = 31 Days in Process

Thanks,

RV

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Posted on Jul 21, 2016 at 03:45 PM

    Try this:

    =DaysBetween([1st Sentdate];[1st Receive Date]) Where (Not(DayName([1st Sentdate]) InList("Saturday";"Sunday")) or Not(DayName([1st Receive Date]) InList("Saturday";"Sunday")))

    +

    DaysBetween([2nd Sentdate];[2nd Receive Date]) Where (Not(DayName([2nd Sentdate]) InList("Saturday";"Sunday")) or Not(DayName([2nd Receive Date]) InList("Saturday";"Sunday")))

    +

    DaysBetween([3rd Sentdate];[3rd Receive Date]) Where (Not(DayName([3rd Sentdate]) InList("Saturday";"Sunday")) or Not(DayName([3rd Receive Date]) InList("Saturday";"Sunday")))

    Add a comment
    10|10000 characters needed characters exceeded

    • I am getting a missing operator or closing parenthesis in ')' at position 186 message when I put in the first part of the formula below...I was going to layer in the other parts subtracting the send/receive date ranges. Can't figure out why I am getting that validation error.

      Option 1


      THIS IS THE OPTION I will utilize. When I put in the first part of the formula only:

      =(DaysBetween([Process End Date];[Process Start Date]) Where (Not(DayName([Process End Date]) InList("Saturday";"Sunday")) or Not(DayName([Process Start Date]) InList("Saturday";"Sunday"))) )





Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.