Calculated column for attributes in formula

[Former Member]

NET DUE DATE CLEAR DATE CURRENT DATE STATUS 1/1/2017 1/2/2017 1/27/2017 LATE 1/31/2017 blank/0000000 1/27/2017 TBP 1/1/2017 1/1/2017 1/27/2017 PAID

Hi Experts - I am trying to achieve this status based on if condition or Case if possible.

Curently I tried using if as below but its not working for 3 conditional values

if(("NETDUEDATE">="CLEAR_DATE" and "CLEAR_DATE"!='00000000'), 'PAID', 'LATE', if("NETDUEDATE"<"CURRENT_DATE" and "CLEAR_DATE"='00000000'),'TBP')

if I remove the 2nd line if condition it works but the TBP (To BE PAID) conddition does not work somehow so need some expert advice.

Thanks in advance for help related to this (using if or case also work)

[Former Member]

Condition given above for reference..

[pfefferf · Accepted solution]

The IF function has only three arguments. You are using five. You have to box the second IF function call in the call of the first IF function.

if(("NETDUEDATE">="CLEAR_DATE" and "CLEAR_DATE"!='00000000'),'PAID',if(("NETDUEDATE"<"CURRENT_DATE" and "CLEAR_DATE"='00000000'),'TBP','LATE'))

Please check also if the condition for "TBP" is correct, because the current one (NETDUEDATE lower than CURRENT_DATE ...) does not match your example for TBP (in your example the net due date is not lower than the current date).

Regards,
Florian

[Former Member · Accepted solution]

I applied this logic

if(("NETDUEDATE">="CLEAR_DATE" and "CLEAR_DATE"!='00000000'), 'PAID', if(("NETDUEDATE"<"DOC_DATE" and "CLEAR_DATE"='00000000' ),'TBP','LATE') )

and there is no error but the current date is not getting populated using CURRENT_DATE so now I need to figure how to get current date in calculation column.

Thanks for your inputs.

[Former Member · Accepted solution]

TBP should only go with CLEAR_DATE = blank because if clearing date is there then the value should be LATE always

now even if the report viewed today then the amount is not cleared the value should be TBP (still payment needs to be done).

[pfefferf · Accepted solution]

The NOW function can be used to get the current date within a calculated column.

Regarding your additional condition for TBP: Just box another IF condition as third argument of the second IF function.

[Former Member · Accepted solution]

Thanks Florian for your time ... really appreciate this!!