0

I'm working in SharePoint 2013.

I created a calculated column of data type = SLOT with the formula:

=IF(Month(Created)>9,Year(Created)+1,Year(Created))

It works but it's returning the as 2,019 (with a comma).

I tried wrapping the TEXT function around the above formula as follows:

=TEXT((IF(Month(Created)>9,Year(Created)+1,Year(Created))),"YY")

but this returns "05"

I don't have access to Notepad++. I tried to troubleshoot in Excel but with no luck either.

0

Change the "The data type returned from this formula is:" to Single line of text and change the formula to this:

=TEXT(IF(MONTH(Created)>9,YEAR(Created)+1,YEAR(Created)), 0)
0

As to why your formula returned 05...

=TEXT((IF(Month(Created)>9,Year(Created)+1,Year(Created))),"YY")
  1. YEAR(somedate) returns something like 2019.
  2. TEXT(2019, "YY") returns the year for the 2019th day from 12/31/1899, which is in the year 1905. ( 7/11/1905 ) The "YY" then returns the "05" of the "1905". TEXT needs a DATE not just a number.

Two possible solutions:

  • Not using the TEXT function:

    ="" & IF( MONTH(Created)>9, YEAR(Created)+1, YEAR(Created) ) the quotes force the result to text.

    or

    =RIGHT( IF( MONTH(created)>9, YEAR(created)+1, YEAR(created)), 2 ) to only return a two digit year.

  • Using the TEXT function in case you are doing other TEXT formatting: (it needs a DATE not just a year number)

    =TEXT( IF( MONTH(created)>9, DATE(YEAR(created)+1,1,1), DATE(YEAR(created),1,1)), "YY")

    a shortened version:

    =TEXT( DATE(YEAR(created) + IF(MONTH(created)>9,1,0),1,1), "YY")

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.