0

I need to calculate the interval between two dates in the SharePoint 2013 list calculated column. the reason is that, i will need to set a field value based on the interval, for example,

if "Current Month" - "Last Reviewed Month" < 6, then set the field value =" green", and if "Current Month" - "Last Reviewed Month" > 6 and = "", then set the field value to "red".

I'm at a loss how to calculate the month range between two dates, for example, how many months between today and April 2015.

1

For calculate formula, calculation happens when you edit the item. That means if you edit the item today, current month will be taken as "June" and formula will show appropriate result. But even if you navigate to the view after 30 days, it will still show result based on "June" and not current month.

This is because calculated column updates the result on item add/edit event.

Workaround to this could be using JSLink to be added on list view which will read the column value and do the calculation to show results on current month basis. Learn more about jslink - JSLink info

1

As @Monica said, calculated columns update only when they are created or modified. You'll get static values, if the columns are not edited. But what you are looking for is DATEDIF method of calculated columns. The syntax will be:

=DATEDIF([date1], [date2], "D")

I'll suggest you to use Timer Job/workflow depending upon you environment.

  • Thanks. I did search thru the internet and indeed there is no a simple solution to show the "current date" dynamically ... as "Today()" function is available in Infopath, can I use it to set the field value in the SharePoint 2013 list? If not, i will test the SP workflow option. – T Zhang Jun 19 '17 at 6:46
  • Even if you use Today() in InfoPath froms, that will work while creating or editing item. So you have to use workflow as an alternative if you have SPO environment. – ThinkB4Code Jun 19 '17 at 6:54

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.