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?

  • 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 5: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 6:16
  • Try this: =IF([Due Date]>TODAY()+7,"In Progress",IF([Due Date]<=TODAY(),"Close Out","Requires Action")) – Amit Bhagat Jan 21 '16 at 7: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

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.

  • 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

  • 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 9: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 9: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 9:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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