Good afternoon everyone,
I am trying to do something that I cant seem to find a solution for after a ton of Googling. I work for a very large company and I am creating a SharePoint site (my first time) for my team and things are going well and I’m learning a lot.Â
Here’s the scenario: I have forms that need to be filled out for our clients. We keep track of a “date received” (when the form shows up in my email from the client), then an “in process” date (when I start filling the form out), and then a “Due Date” (when the form is scheduled to be done, which is currently set for 14 days from the “In Process Date”)
Right now, I have a column for each of the 3 dates mentioned above. The “Date Received” and “In Process” dates are entered by selecting the date from the built in calendar I see when I go to “Edit Item” I have a few things I am wanting to do with the date stuff, but right now I am focused on stoplight color coding. Due Date is a calculated column that adds 14 (days) to the “In Process” date which then is calculated and displayed as the “Due Date”
What I want to do, is for the dates under “Due Date” to be green (preferably the dates and not the background, but willing to do either)Â when date is say 14-9 days away from the due date, then yellow or amber when 8-5 days away from the due date, and then lastly red when 4-0 days from due date and even when its past due date.
In sudo form I guess this is what I am looking to do
If “In Process” date is >= 9 days from “Due Date”
Then calculated due date = green
If <today’s date> is >= 5 days from “Due Date”
Then calculated due date = amber
If <today’s date> is <= 4 days from “Due Date”Â
Then calculated due date = red
I don’t know if that muddied up the waters more, but hopefully you get the idea. Any help figuring this out would be greatly appreciated. I should not that some things, like workflow, is disabled and I don’t have access to.
@Wayne: If you are using Office 365, then try with SPFx Extensions (Field Customizer) : https://docs.microsoft.com/en-us/sharepoint/dev/spfx/extensions/get-started/building-simple-field-customizer
Modern views give you all together a new look & feel with new features to your site. If you are using a modern view, you can do colour coding using column formatting option described here.
If you are still in classic view, you could achieve this by writing a custom script and adding it to the page using a content editor webpart.
Can you specify what version of SharePoint you are using? Depending onthe version, you’ll get a different answer. If classic SharePoint, you’ll be using Client Side Rendering using JSLink to build these KPI (javascript), or in Modern you can do this using Column Formatting. When you say you tried out some code… do you think you could post the code for us to see/review to help out?
Are you looking to have this color code in the item detail view (ie new/edit/display) or the list view? Also do you have modern views enabled??
Are you looking to have this color code in the item detail view (ie new/edit/display) or the list view? In the list view
Also do you have modern views enabled?? I have heard of “modern views” but have no idea what that is, or how to enable it but I will do some Googling to learn more about it. Is it something that I should enable?
I should have added, if it makes a difference, that my “queue” is using “Issue Tracking”
Im pretty certain its the modern view. With that said, I will look at the page you pointed me to and see what I can do with whats provided. I think the one thing I am having a hard time thinking through is that my “Due Date” column is a calculated column that spits out a date using the formula =[In Process]+14
The part that I am trying to figure out, at least when I try to think about it, is that “14” is a variable that will never change, unless of course we were to edit that to something else, but either way its a static number. If I am understanding correctly and based on some of the code I have read, I may actually need to compare today’s date with “Due date”. Something like:
if the difference between today’s date and due date >=9 then color it green.
If the difference between today’s date and due date is 5 then color it amber. If the difference between today’s date and due date is <=4 then color it red
I did try a couple of snipits of code and made sure to add the _x0020_ to my "due date", but the one thing I noticed is that the code will color the field it's in, and not color the due date. But in any case I was not able to get it to work. I also didn't see that any of the code followed the sudo, assuming that flow is correct. I am wanting to color the due date, and more specifically the date text (ie 4/20/2018) if at all possible.
Thank you so much for the help thus far.