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#?

Asad Refai
  • 5,971
  • 8
  • 34
  • 57
Nderon Hyseni
  • 1,793
  • 6
  • 35
  • 63
  • 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 01 '15 at 11:59

4 Answers4

5

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

Robert Lindgren
  • 24,520
  • 12
  • 53
  • 79
Gaurravs
  • 3,558
  • 12
  • 22
  • 33
  • 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 01 '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 02 '15 at 14:24
  • @NderonHyseni Glad my solution helped you – Gaurravs Oct 02 '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 05 '15 at 05:39
2

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.

P S
  • 4,827
  • 4
  • 27
  • 50
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).

Community
  • 1
Atish Kumar Dipongkor
  • 13,371
  • 5
  • 32
  • 64
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

Danny '365CSI' Engelman
  • 21,176
  • 7
  • 35
  • 79