Use SharePoint format to add a column status by reading data from different column

[Musse93]

Hi everyone. I want to use format column to read data from a column called Notice Date and if the Notice Date is within 1 week it should create status column with text "urgent" with red color, if Notice date is within 2 weeks it should create "important" column with yellow color and if it's 30 days or more it should create "not urgent" column with green color. Can anyone help me with this?

Best regards.

[DukeChen]

Hi @Musse93 ,

Have a question I want to confirm with you: the color and status depend on the different of between Today and Notice Day. But “Today” is a dynamic value. Are you want to refresh the status or color every day?

Duke

[Musse93]

Hi @DukeChen Correct both color and status is depending on the Notice Day. I want both the status and color of the status text to be changed. if NoticeDate <= Now + 7 && NoticeDate > Now, then Status column should read "Urgent".

If NoticeDate > Now +7 && NoticeDate < Now +14 , then status column should read "Important"

If NoticeDate > Now + 30, then status should read "Not important"

If NoticeDate "  ", then status should not read.

[DukeChen]

Hi @Musse93 ,

I can understand your request and you can see solution in  Power Automate.

But if NoticeDate > Now +14 && NoticeDate < Now +30,it seems have no status.

Power Automate Solution:

Build a scheduled cloud flow and config it run every day at 12:00AM

Select "Get items" to get all item in your SharePoint List.

Set DifferentInDay-String expression:

div(sub(ticks(variables('Notice Date')),ticks(variables('Today'))),864000000000)

Set DifferentInDay-Int expression:

int(variables('DifferentInDay-STR'))

The DifferentInDay-Int is the number of days that differ between the notification day and today.

You can set the condition according to your rules.

Hope can help you....

Duke

[Musse93]

@DukeChen Thanks a lot! wouldn't it be easier to do this task with SharePoint column format function instead of Power Automate ?

[DukeChen]

Hi @Musse93 ,

Of course, you can use SharePoint column format function to calculated status. 

Add Column [DateDiff] Type choose "Calculated (calculation based on other columns)"

Formula:=[Notice Date]-TODAY()

Add Column [Status Calculate.] Type choose "Calculate (calculate base on other columns)"

Formula:=IF(DateDiff<7,"Urgent",IF(AND(DateDiff>7,DateDiff<14),"Important",IF(DateDiff>30,"Not important","")))

If you want to add color remind, you can use "Format columns" to set you rules.

Hope can help you !

Duke

[Musse93 · Accepted solution]

@DukeChen Thanks a lot! Your way looks much easier. I was only using column formatting to do all the works, instead of creating "calculated column". However I don't manage to create the "Status Calculate" column. I keep getting syntax error saying. 

Sorry, something went wrong

The formula contains a syntax error or is not supported.

[DukeChen · Accepted solution]

HIi@Musse93 ,

Please check [DateDiff] setting : The data type return from this formula is choose Number and confirm that the [DateDiff] value is right.

In the other hand, you also can try the formula ：

=IF([DateDiff]<7,"Urgent",IF(AND([DateDiff]>7,[DateDiff]<14),"Important",IF([DateDiff]>30,"Not important","")))

Hope can help you ...

Duke

[Musse93]

Hi @DukeChen It is working now. I had to use ; instead of ,  

Thank you so much I really appreciate help and effort!

Best regards.