# How to calculate 12 months from a start date

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

• 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. Oct 1, 2015 at 11:59

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? Oct 1, 2015 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. Oct 2, 2015 at 14:24
• @NderonHyseni Glad my solution helped you Oct 2, 2015 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, 2015 at 5:39

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.

``````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){

}
``````

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

Note:

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

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

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(',');"
&    "var ctx=SP.ClientContext.get_current();"
&    "var list=SP.ListOperation.Selection.getSelectedList();"
&    "var item=ctx.get_web().get_lists().getById(list).getItemById(ID);"
&    "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