0

I created two calculated columns to calculate the difference between Payment Date and Transfer Date, one of which is the "Duration" (between the payment and transaction) and the other is "Days Since Payment" (In case the transaction is not done).

My question is that CAN I make the duration column and the Days Since Payment skip and not count the weekends?

Say Payment Date is OCT 27 2019 .. TODAY IS NOV 5 2019 .. the Duration should be 7 days if we exclude the weekend.

Here are the formulas I applied: Duration Column =IF(ISBLANK([Transfer Date]),"N/A",DATEDIF([Payment Date],[Transfer Date],"d"))

Days Since Payment =IF(ISNUMBER([Duration (Days)]),"Closed Transaction",DATEDIF([Payment Date],TODAY(),"d"))

Help please and thank you!

0

Try this formula to exclude the weekend:

=(DATEDIF([Payment Date],[Transfer Date],"d"))+1-INT(DATEDIF([Payment Date],[Transfer Date],"d")/7)*2-IF((WEEKDAY([Transfer Date])-WEEKDAY([Payment Date]))<0,2,0)-IF(OR(AND(WEEKDAY([Transfer Date])=7,WEEKDAY([Payment Date])=7),AND(WEEKDAY([Transfer Date])=1,WEEKDAY([Payment Date])=1)),1,0)-IF(AND(WEEKDAY([Payment Date])=1,(WEEKDAY([Transfer Date])-WEEKDAY([Payment Date]))>0),1,0)-IF(AND(NOT(WEEKDAY([Payment Date])=7),WEEKDAY([Transfer Date])=7),1,0)

Test Result:

enter image description here

  • Thank you! It works. – Rami Nov 12 '19 at 10:49
  • Please accept the answer if it helps you. – Michael Han_MSFT Nov 12 '19 at 13:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.