Calculated column to return text string

Question

Hi Team

I've built a view in a modern SharePoint list. The objective of this view is to manage the data drift between the SharePoint list and the source of truth database called GRACE. My aim to do an extract daily and cross reference these columns against each other to ensure the data is kept up to date.

I've used calculated columns to highlight any cell that isn't matching with the help of conditional formatting to make things stand out more. I require a calculated formula to return a specific string of text when list items don't match up with the GRACE columns I've listed below. The data I upload into these 3 GRACE columns are a free text field

As an example, would be if the Go Live dates don’t match as well as the CDT Rating. I want it to return the value of “Updates done correctly to reflect GRACE data. Go Live updated from 29/02/2024 to 03/03/2024. CDT Score updated from High to Low” which then I can copy this text and paste it in the comments section as opposed to typing this out, which will save a lot of time

Another example, if the list item doesn’t match with a GRACE column such as the Go Live dates. I want it to return a value off “Updates done correctly to reflect GRACE data. Go Live updated from 18/02/2024 to 07/03/2024”

When there’s No Match values- I want the start of text string to return the wording of “Updates done correctly to reflect GRACE data” followed by if there’s a date mismatch- “Go Live updated from (Go Live date column) to (GRACE Go Live date column)” if there’s a CDT Rating mismatch, I want it to return the text string of “CDT Score updated from (CDT Risk Impact column) to (GRACE Risk Impact column)

if the item has been Withdrawn under the GRACE Status Column, I want it to return “Updates done correctly to reflect GRACE data. Change has been Withdrawn”

if the item has been Completed under the GRACE Status column, I want it to return “Updates done correctly to reflect GRACE data. Change has been Completed”

I provided a screenshot of my expected result which is shown in the far right column in a testing environment.

Thanks
Harry

Answer 1

Hi @Harry N Nomikos,

Based on your description, is the "Change has been Completed/Withdrawn" statement added without first determining if the GRACE Go Live Date matches the Go Live Date and if the GRACE CDT Rating matches the CDT Risk Impact? (Because my original judgment added)

If so, try using this modified formula below:

="Updates done correctly to reflect GRACE data. "&IF([GRACE Go Live Date]=[Go Live Date],"","Go Live updated from "&TEXT([Go Live Date],"dd/mm/yyyy")&" to "&TEXT([GRACE Go Live Date],"dd/mm/yyyy")&".")&IF([GRACE CDT Rating]=[CDT Risk Impact],"","CDT updated from "&[CDT Risk Impact]&" to "&[GRACE CDT Rating]&".")&IF([GRACE Status]="Withdrawn","Change has been Withdrawn.","")&IF([GRACE Status]="Completed","Change has been Completed.","")

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Harry N Nomikos,

Thank you for posting in this community.

The type of column I'm testing:

GRACE Go Live Date & Go Live Date: Date and time.

GRACE CDT Rating &CDT Risk Impact&GRACE Status: Single line of text.

You can use this formula:

="Updates done correctly to reflect GRACE data. "&IF([GRACE Go Live Date]=[Go Live Date],"","Go Live updated from "&TEXT([Go Live Date],"dd/mm/yyyy")&" to "&TEXT([GRACE Go Live Date],"dd/mm/yyyy")&".")&IF([GRACE CDT Rating]=[CDT Risk Impact],"","CDT updated from "&[CDT Risk Impact]&" to "&[GRACE CDT Rating]&".")&IF([GRACE Go Live Date]=[Go Live Date],IF([GRACE CDT Rating]=[CDT Risk Impact],IF([GRACE Status]="Withdrawn","Change has been Withdrawn.",IF([GRACE Status]="Completed","Change has been Completed.","")),""),"")

Here is the result of my test:

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @Harry N Nomikos,

Thank you for your reply.

We just need to add this formula after the original one: &IF([GRACE Title]=Title,""," Initiative Title updated.")

Here is my test result:

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.