2

I am building an application about workers vocation and I need some help on calculating.

Here is what I need:

I have a column named startdate, after 12 months I want the SharePoint or c# to know dynamical when 12 months has gone.

Example the start date of worker1 is 01/01/2015 after 12 months or 1 year I want that worker1 to get a checked box = true,

And worker2 start date is 05/03/2015 after one year or 12 months same thing to checked = true

How to do it on SharePoint via formulas or in C#?

  • 1
    If I rephrase your question to "Make SP do something after 12 months" you don't need a calculated column, but a Workflow or some code behind which checks the date and sets the Checkbox. – Danny '365CSI' Engelman Oct 1 '15 at 11:59
4

I guess this should work for you,

Create a calculated column with such formula

    =DATE(YEAR([Created])+1,MONTH([Created]),DAY([Created]))

Do not add months to this, else you will get a wrong result in some case.

Once you have this column with values, you can create a timer job/workflow to run every day.With minimum logic, just need to check if that column value (Calculated column) is equal to todays date, and you are good to update the Yes/No field value to yes. ie in your case "checked = true"

Above solution will be mimimal in terms of implementation. You can remove the calculated column logic if you want, and do calculation in your workflow/timer job

  • Altough I also stick to your solution because it is much clearer, could you Point out a case where adding months would produce a wrong result? – Leopold Lerch Oct 1 '15 at 13:58
  • Hi Leopold, Consider Created date as 01-Dec- 2015, if u add 12 month into the formula. it would appear as =DATE(YEAR([Created]), MONTH([Created]) + 12 , DAY([Created])) =DATE(2015, 12 + 12, 01 ) = DATE(2015 , 24, 01) OR Consider Created date as 01-Dec- 2015, if u want to add 2 month into the formula. it would appear as =DATE(YEAR([Created]), MONTH([Created]) + 2 , DAY([Created])) =DATE(2015, 12 + 2, 01 ) = DATE(2015 , 14, 01) if in both cases even if it convert it to appropriate month, the year remains same, which ideally should be next year. – Gaurravs Oct 2 '15 at 14:24
  • @NderonHyseni Glad my solution helped you – Gaurravs Oct 2 '15 at 14:28
  • Hi Gaurav, example given in comments for month addition is incorrect, The formula is given at MSDN site. And I just created calculated column with your example and it worked. – P S Oct 5 '15 at 5:39
1
public static int MonthDifference(this DateTime lValue, DateTime rValue)
{
    return (lValue.Month - rValue.Month) + 12 * (lValue.Year - rValue.Year);
}

Note that this returns a relative difference, meaning that if rValue is greater than lValue, then the return value will be negative. If you want an absolute difference, you can use this:

public static int MonthDifference(this DateTime lValue, DateTime rValue)
{
    return Math.Abs((lValue.Month - rValue.Month) + 12 * (lValue.Year - rValue.Year));
}

taken from here. Now using it like following.

var monthDifference = MonthDifference(DateTime.Now, startdate);

if(monthDifference >= 12){

//implement your logic here

}

If you wish to make it by calculated column then use DATEDIF(start_date,end_date,unit).

1

Create a calculated column with below formula:

=DATE(YEAR([Start Date]),MONTH([Start Date])+12,DAY([Start Date]))

This will add 12 months to your start date. Ref: Calculated Field formulas

Now to make check-box true, either you have to use designer workflow or timer job/console application.

1

Note:

in June 2017, Microsoft disabled the use of JavaScript in a Calculated Column

That means given answers may not apply for newer SharePoint versions

For long explanation and work arounds see:
June 13th 2017 Microsoft blocked handling HTML markup in SharePoint calculated fields - how to get the same functionality back


Original answer:

You can check for expired/today dates without a Workflow or Timerjob if you:

  • Do the calculation in JavaScript (remember JS months start at 0 for january)
  • Have a View that is displayed daily (by a user with update permissions)

Add a Calculated Column (set to datatype=Number to execute the HTML/JS)

=IF(CheckedBox,"","<img src=/_layouts/images/blank.gif onload=""{"
&"var SPday=new Date("   
&YEAR([Created])    
&","    
&MONTH([Created])-1    
&","    
&DAY([Created])    
&");"
&"if(new Date() > new Date( SPday.getFullYear(), SPday.getMonth()+12 ) ){"
&    "var TR=this;while(TR.tagName!='TR'){TR=TR.parentNode}"
&    "var ID=TR.id.split(',')[1];"
&    "var ctx=SP.ClientContext.get_current();" 
&    "var list=SP.ListOperation.Selection.getSelectedList();" 
&    "var item=ctx.get_web().get_lists().getById(list).getItemById(ID);" 
&    "ctx.load(item);" 
&    "item.set_item( 'CheckedBox' ,'TRUE' );" 
&    "item.update();" 
&    "ctx.executeQueryAsync();"
&"}"
&"}"">")
  • This code is only generated for CheckedBox==False/No values
  • If the code is added (to every ListItem in the View)
  • it compares the dates
    • if the date is past today
    • it updates the current listitem using JSOM (so the Modified By data is the current user)

All Pros and Cons of mixing Calculated Columns with Javascript at http://www.viewmaster365.com/#/How

ICC 010

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.