0

I have tried to apply the following formula in the SharePoint list but somehow it gives an error. Can the same formula be used or any changes are to be made?

Please help me out. I have given the formula in the below picture and as text:

=IF(AND(D4="Approved",E4="Approved"),"Approved",
IF(AND(D4="Approved",E4<>"Approved"),"Awaiting HR Approval",
IF(NOT(ISBLANK(C4)),"Awaiting GM Approval",
IF(TODAY()>B4,"Awaiting PV Creation",
IF(TODAY()<B4,"Awaiting Payment Terms","Due Date"
)))))

enter image description here

Please let me know if you need any clarification.

2
  • Your formula syntax looks fine to me. Make sure you are using actual column names instead of B4/C4/D4, etc. In formula, you have to use column names surrounded by square brackets like [HR Approval Status]. Also, are you getting any error message related to using Today() function in formula? May 27, 2022 at 12:41
  • Using Today in calculated columns won't work like you think. Calculated columns are only evaluated on new/update - not on display. You can, however, use column formatting to solve this and it will be evaluated on render. May 27, 2022 at 13:53

2 Answers 2

0

The use of Today() in calculated columns will not behave like you hope. If you really want to store a calculated value based on the date of the new/update action you can use the Modified column for that.

Otherwise, you'll want to solve this with column formatting. I still recommend creating a calculated column with a formula of ="" and then hiding it from your list form. This allows you to have a column with a format attached without having to store values. Alternatively, you can just add any regular column and apply the format to it (since we're not using the value).

Here's a format to show the status using your formula:

{
  "$schema": "",
  "elmType": "div",
  "txtContent": "=if([$D4]=='Approved' && [$E4]=='Approved', 'Approved', if([$D4]=='Approved', 'Awaiting HR Approval', if(length([$C4]),'Awaiting GM Approval',if(@now > [$B4], 'Awaiting PV Creation', if(@now < [$B4], 'Awaiting Payment Terms', 'Due Date')))))"
}

In the above, the assumption is that your column's internal names are D4, B4, etc. This doesn't seem likely. You'll need to replace those with the actual internal names to get this work.

0

On the Settings page, under Columns, select Create Column. In the Name and Type section, enter the name that you want in the Column name box. Under The type of information in this column, click Calculated.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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