 # Sum elapsed time between events, excluding weekends, for each customer in column

• I've seen many related posts but none that address my problem exactly. I have a column of customers who applied for a loan. During the approval process each loan goes through a number of stages denoted by a change in the status. I have columns that track the time between stages. I'd like to summarize the total time between the start and end of the application process excluding Saturday's and Sunday's (ideally just Sunday's if the last event is on a Saturday). I have a formula that can do it using the NETWORKDAYS function but it requires knowing the row of the first and last date. So what I (think I) need is souped up version of this formula that recognizes the change in the customer's name in column C and incorporates those rows as the start/stop points. I've been playing with it for the last couple days but I haven't been able to figure it out. Any assistance greatly appreciated!

The formulas in the example is: =NETWORKDAYS(K40,[@[Date/time Status Changed]])-1-MOD(K40,1)+MOD([@[Date/time Status Changed]],1) where K40 is the first row of that customer and [@[Date/time Status Changed]]is the last row. Without the table notation the formula for that customer would be: =NETWORKDAYS(K50,K54)-1-MOD(K50,1)+MOD(K54,1).

I've also attached a worksheet with the formulas I have so far.

## Files

• Re: Sum elapsed time between events, excluding weekends, for each customer in column

Hi Jeffy,

Try this:

Past this formula into Cell G2 and drag down in order to let Excel refill the formulas automatically

=> =IF(A2=A3,"",IF(TEXT(D2,"ddd")="Sat",(NETWORKDAYS(VLOOKUP(A2,\$A:\$D,4,FALSE),D2)-1-MOD(VLOOKUP(A2,\$A:\$D,4,FALSE),1)+MOD(D1,1)+E2),NETWORKDAYS(VLOOKUP(A2,\$A:\$D,4,FALSE),D2)-1-MOD(VLOOKUP(A2,\$A:\$D,4,FALSE),1)+MOD(D2,1)))

Hope it will bring the result you want

Thank

• Re: Sum elapsed time between events, excluding weekends, for each customer in column

That's fantastic. It works beautifully. Thank you so much!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!