There were similar question on this thread but none answers the question I've have. Hope someone could help.
I've two formulas, one formula helps to calculate the time difference by excluding the weekends and other formula helps to calculate the time difference without excluding anything.
Formula 1: =NETWORKDAYS(W2,X2)-1-MOD(W2,1)+MOD(X2,1)
Formula 2: =TEXT(X10-W10, "[h]:mm:ss")
In column "W", I've Opened Date in following format "11/24/2020 1:14 AM" , In column "X" I've closed date in same format.
In column "Z" I've the formula picker, the formula picking conditions are: If value in column "Z" is: CAT1 apply formula 2 CAT2 apply formula 2 CAT3 apply formula 1 CAT4 apply formula 1
You can use an IF for this:
=IF( OR (Z1 = "CAT1" , Z1 = "CAT2") , put formula 2 here , put formula 1 here )
For the full working formula:
=IF( OR (Z1 = "CAT1" , Z1 = "CAT2") , TEXT(X10-W10, "[h]:mm:ss") , NETWORKDAYS(W2,X2)-1-MOD(W2,1)+MOD(X2,1) )
For error trapping as per comment use:
=IFERROR(IF( OR (Z1 = "CAT1" , Z1 = "CAT2") , TEXT(X10-W10, "[h]:mm:ss") , NETWORKDAYS(W2,X2)-1-MOD(W2,1)+MOD(X2,1) ),TEXT(X10-W10, "[h]:mm:ss"))
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments