1

I'm trying to create a tracking list to track the status of jobs using SharePoint Online.

I want to use the categories:

'In Progress' = 7 days + from Due Date
'Requires Action' = Within 7 days from Due Date
'Close Out' = After Due Date

I have set up conditional formatting using Content Editor - Web Part. I am now trying to set up a 'Status' column that returns the correct outcome based on a time frame.

I have the following formula:

=IF(Date+7<[Due Date],"In Progress","")&IF([Due Date]<=Date,"Close Out","")

Note: Date = Today's date

This delivers the desired results for 'In Progress' and 'Close Out', however I am uncertain as to how to deliver the 'Requires Action' outcome between the two time periods. What should I add to the formula?

Asad Refai
  • 5,971
  • 8
  • 34
  • 57
coleman
  • 13
  • 3
  • If i understand you correctly you simply want a different string if between the two dates. Simply do a >= and <= in one if statement. – Roland Jan 21 '16 at 05:35
  • Thanks for your reply Roland - how exactly should I add that to my formula above? I've tried but it keeps returning an error. – coleman Jan 21 '16 at 06:16
  • Try this: =IF([Due Date]>TODAY()+7,"In Progress",IF([Due Date]<=TODAY(),"Close Out","Requires Action")) – Amit Bhagat Jan 21 '16 at 07:12
  • Thought I would mention that calculated columns work (for the most part) just like excel functions. To quickly make a new formula simply test it in excel which is much quicker to do. – Roland Jan 21 '16 at 21:59

2 Answers2

2

I tried it as the following example in my SharePoint 2013 on-prem.

If you want to use the today() date, the formular looks like this:

=IF(AND([Due Date]>Today(), [Due Date]<Today()+7), "Requires Action", "")&IF(Today()+7<[Due Date],"In Progress", "")&IF([Due Date]<=Today(),"Close Out", "")

And my list looks e.G. like this:

enter image description here

Keep in mind, that the today() doesn't work as you might expect. See here.

With seperate date column my list looks like this:

enter image description here

The column settings are:

Title -> Single line of text

Date -> Date only (Not required if using today())

Due Date -> Date only

Status -> calculated column (returned as single line of text)

The fomular I used in the status column is: 

=IF(AND([Due Date]>[Date], [Due Date]<[Date]+7), "Requires Action", "")&IF([Date]+7<[Due Date],"In Progress", "")&IF([Due Date]<=[Date],"Close Out", "")

If this helps you you can give it a vote up or if it fit's perfectly you can accept it as your answer.

Community
  • 1
Patrick
  • 3,203
  • 3
  • 24
  • 42
  • Also, while using this trick I wrote a system.update powershell script. This would update all the items daily thus updating the today() date. – Taran Goel Jan 21 '16 at 10:11
0

Formulas will only update when the List Item is modified/updated.

That means Today() is the same as [Modified] (last date the item was modified)

For long explanation and possible workarounds read:

How to use Today and Me in Calculated column

Community
  • 1
Danny '365CSI' Engelman
  • 21,176
  • 7
  • 35
  • 79
  • Please don't add the exact same answer to this question, as you did on this one. Instead, try to specify your answer further to make it a better of this specific question. Thank You! – Benny Skogberg Jan 21 '16 at 09:40
  • Then I choose to not answer at all. Why should I put more and more effort into it if no-one searches before asking – Danny '365CSI' Engelman Jan 21 '16 at 09:55
  • 1
    Or add it as a comment, in this case I guess you just want to spread the knowledge about the workings of Today(), not answer the full question. Comments are great for that :) – Robert Lindgren Jan 21 '16 at 09:56