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.
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.
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.