I am trying to set up a formula field (field name: Delta) with the logic I've listed below.
So far I have been doing the formula in excel and want to move it to a Caspio field called Delta. The excel formula is:
=IF(AND(G2="",G2<>DATEVALUE("9/9/9999"),F2="No",H2<>""),DAYS(H2,I2),IF(AND(G2<>"",G2<>DATEVALUE("9/9/9999"),F2="No"), DAYS(G2,I2),IF(AND(G2=DATEVALUE("9/9/9999"),F2="No"), DAYS(E2,I2),IF(AND(G2<>"",G2<>DATEVALUE("9/9/9999"),G2<E2,F2="Yes"), DAYS(G2,I2),IF(AND(G2>E2,F2="Yes"), DAYS(E2,I2),IF(AND(H2=""), "NA","TBD"))))))
The fields referenced in the formula are:Actual Test Bank Install (date/time datatype)Estimated Test Bank Install (date/time datatype)Contract Test Bank Install (date/time datatype)RTS Actual (date/time datatype)Released to Sales (formula datatype that contains either Yes, No or NA as the value)
The written out logic for Caspio is:
-----------------------------------------------------------------------------If [@field:Actual Test Bank Install] is null and [@field:Actual Test Bank Install] is not “09/09/9999” and [@field:Released to Sales] = ‘No’ and [@field:Estimated Test Bank Install] is not blankThen calculate the number of calendar days between [@field:Estimated Test Bank Install] and [@field:Contract Test Bank Install]If [@field:Actual Test Bank Install] is not blank and [@field:Actual Test Bank Install] is not “09/09/9999” and [@field:Released to Sales] = ‘No’Then calculate the number of calendar days between [@field:Actual Test Bank Install] and [@field:Contract Test Bank Install]If [@field:Actual Test Bank Install] is “09/09/9999” and [@field:Released to Sales] = ‘No’Then calculate the number of calendar days between [@field:RTS Actual] and [@field:Contract Test Bank Install]If [@field:Actual Test Bank Install] is not blank and [@field:Actual Test Bank Install] is not “09/09/9999” and [@field:Actual Test Bank Install] is less than (or before) [@field:RTS Actual] and [@field:Released to Sales]= ‘Yes’Then calculate the number of calendar days between [@field:Actual Test Bank Install] and [@field:Contract Test Bank Install]If [@field:Actual Test Bank Install] is greater than (or after) [@field:RTS Actual] and [@field:Released to Sales]= ‘Yes’Then calculate the number of calendar days between [@field:RTS Actual]and [@field:Contract Test Bank Install]If [@field:Estimated Test Bank Install] is blankThen ‘NA’Else ‘TBD’-----------------------------------------------------------------------------
The formula for the Released to Sales field is:CASE WHEN convert(nvarchar(10), [@field:RTS_Actual], 101) = '09/09/9999' THEN 'NA' WHEN convert(nvarchar(10), [@field:RTS_Actual], 101) != '09/09/9999' AND [@field:RTS_Actual] is not null THEN 'Yes' ELSE 'No' END
-----------------------------------------------------------------------------
Can someone help with what the Caspio case statement would be for my field?
Thanks in advance!
Anna