Search code examples
sharepointsharepoint-2013

Formula to get the last date(30th or 31st) of the added month to a date in SharePoint calculated column

I have a column name DateOfJoining and another column EndProbation. When Date is filled in DateOfJoining Field (Eg: 5th June 2016) the last date of the 6th month added should be displayed in the EndProbation(Calculated column) column.(When 6 month is added it will be 5thDec but it should be 31st December). Please help me out to write Formula in calculated column.

Thanks in Advance.

Solution

  • If you want to display the last day of the month, you can use a formula like this:

    =DATE(YEAR([column]),MONTH([column])+1,0)

    There are three parameters in that DATE() function:

    • The first one is the year. YEAR([column]) says to show the year from the [column] column (which should be a date field)
    • The second one is the month. MONTH([column])+1 says show me the month after the month from [column]
    • The third one is the date. 0 says show me the day before the first day of the month (the 0th day of the month instead of the 1st day of the month).

    If you need to show the last day of the month six months from now, that'll be pretty similar:

    =DATE(YEAR(A1),MONTH(A1)+7,0)

    The only difference is that you increase the month parameter by 7 instead of by 1.