2

I have been asked to apply conditional formatting to a list in SharePoint 2010 to change the font colour if the difference is 7 or less when you minus the date in the Date Valid column from todays date. So...

Today – Date Valid <= 7

I can apply simple conditions but I think the one I have been asked for would need to be done in the Advanced Condition window and I haven't been able to figure out what it would be.

Does anyone know what the condition would be?

Hardik
  • 7,733
  • 2
  • 18
  • 37
Ashleigh Symonds
  • 33
  • 3

1 Answers1

0

I would suggest making a calculated column called Today - Date Valid and that does your calculations for you. Just hide the column from any view so you don't have this data showing up.

Then go into SharePoint Designer and do your Conditional Formatting where Today - Date Valid is the Field Name, Less Than Or Equal is your Comparison, your Value can be edited be clicking Advanced and putting in 7 (or you can create a new parameter and set its value to 7, not sure if this will work but you can try), and then going and setting your style.

Hope this helps

edit: you can use this for reference https://blogs.msdn.microsoft.com/balsharfi/2011/10/20/sharepoint-lists-conditional-formatting-using-sharepoint-designer-2010/

user54272
  • 96
  • 5
  • Thank you for your help. As far as I know you cannot use [Today] in a calculated column. You get error “Calculated columns cannot contain volatile functions like Today and Me”. – Ashleigh Symonds Aug 16 '16 at 13:26
  • You can use [Today] in calculated columns! For difference calculations for dates you would need to use =DATEDIF as per https://msdn.microsoft.com/en-us/library/office/bb862071(v=office.14).aspx – user54272 Aug 16 '16 at 13:29
  • apparently, to use TODAY as you would in Excel, it would be Today() as per http://sharepoint.stackexchange.com/questions/151144/how-to-use-today-and-me-in-calculated-column – user54272 Aug 16 '16 at 13:39
  • Oh ok sorry, maybe I am misunderstanding. I tried creating a calculated column, once with formula =[Today]-[Date Valid] and once with formula =DATEDIF([Date Valid],[Today],"d") and both times I get the error “Calculated columns cannot contain volatile functions like Today and Me” – Ashleigh Symonds Aug 16 '16 at 13:40
  • Unfortunately it does not work as required for me. As the article states, the ‘Today’ value will not automatically refresh and the list item would have to be edited for the correct value to show each day. It is a useful post though that has suggestions for a resolution to this, thanks for pointing me to it. – Ashleigh Symonds Aug 17 '16 at 08:46