Newbie Questions

Re: Conditional Format with Date (if more than 31 days, change color to RED)

benny.mcpherson — Tue, 01 Sep 2015 02:19:12 GMT

Thanks, worked for me.

Re: Conditional Format with Date (if more than 31 days, change color to RED)

Anson Hidajat — Tue, 03 Apr 2012 20:18:16 GMT

The concept should be similar with an expression box. Instead of using a validation rule, you can use the formatting rule the same way and make the background red or something. Sorry I'm not sure what your exact scenario here is, so I'm making a few assumptions in my explanations :)

Re: Conditional Format with Date (if more than 31 days, change color to RED)

TxSteve — Tue, 03 Apr 2012 17:26:09 GMT

Thank you very much for the detailed instructions, but sadly, these are expression boxes. I can't select them from the AddDays picker. Maybe I need to approach this from a different perspective. Should I create a text field and then try to associate it?

Re: Conditional Format with Date (if more than 31 days, change color to RED)

Anson Hidajat — Tue, 03 Apr 2012 17:01:35 GMT

Sure I can walk you through it. I'm assuming both are date fields that are bound to date pickers. Here's what you need to do:

Click on the first date picker.
Open up the Rules Manager (Home -> Manage Rules)
Add a validation rule.
Click on the condition which will bring up a dialog
For the left dropdown, select the first date field.
For the middle dropdown, select is less than.
For the right dropdown, select use a formula.
Click Insert Function --> Date category -0> addDays --> OK
Double click on the first field and select the second date.
Double click on the second field and type in "-30".
OK out of all the dialogs
Make sure you enter a ScreenTip otherwise the rule won't work.

Hope this helps!

Re: Conditional Format with Date (if more than 31 days, change color to RED)

TxSteve — Tue, 03 Apr 2012 16:41:55 GMT

I apologize, I follow what you are saying, just not sure how to write it. Would it be something like (@Created) +31. I tried this, and it passes the formula checker but it does not make it go red.

Re: Conditional Format with Date (if more than 31 days, change color to RED)

Anson Hidajat — Tue, 03 Apr 2012 16:27:14 GMT

Then it should just work if you use that date directly from SharePoint instead of parsing it the way you do. Try that and see if that works for you now.

Re: Conditional Format with Date (if more than 31 days, change color to RED)

TxSteve — Tue, 03 Apr 2012 16:20:46 GMT

It's pulling the created date from a SharePoint list. I had to set it up this way because in a new form, there is no created date. So the field is hidden. However, if after its creation the form is opened by a user, the created field is displayed. Then there is the Expiration field. It too has the same hidden condition because with a new form there is no expiration. Again, once opened it displays a Created Date + 30 days value. What I am trying to do is have it display, but on the 31st day, make it go red.

Hope that better explains things.

Re: Conditional Format with Date (if more than 31 days, change color to RED)

Anson Hidajat — Tue, 03 Apr 2012 16:15:05 GMT

What exactly are you using to create your date field? In my formula all you need is just a simple date for it to work. You don't need to parse the different parts separately.

Re: Conditional Format with Date (if more than 31 days, change color to RED)

TxSteve — Tue, 03 Apr 2012 14:53:01 GMT

Thank you for your explanation. Sadly, I am still struggling a bit. Perhaps a little more explanation may be helpful.

Created: (Expression Box) concat(substring(@Created, 6, 2), "/", substring(@Created, 9, 2), "/", substring(@Created, 1, 4))
Expired: (Expression Box) addDays(@Created, 30)

Within the Expired box I am adding the Condition. It seems I can either get the font to always be black (default) or always red. :( The always red setting is configured as follows;

If this Condition is true:
Created Is Greater Than adddays(@created, -30)

I've got to be close, it's just not seeing or utilizing the date. It should only go red on the 31'st day.

Re: Conditional Format with Date (if more than 31 days, change color to RED)

Anson Hidajat — Mon, 02 Apr 2012 19:44:23 GMT

You need to add conditional formatting to your date picker. The formula you will use will look something like if date1 < addDays(date2, -30). You'll have to play around with the number to get it exactly how you want it, but that's the general idea.

Conditional Format with Date (if more than 31 days, change color to RED)

TxSteve — Mon, 02 Apr 2012 16:41:45 GMT

I have two fields. One displays a created date. The second is an expression field that looks at the created date, adds 30 days and displays that date. What I want is for the expression box to go red if the count is 31 days or more. What is the best way to accomplish this?