# 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

• 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
• R Var Kuldeep Ghosh

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