none
Help with calculated column formula- combine strings and convert to date

    Question

  • In my list (SharePoint Server 2013) I have:

    'Invoiced Month' column, type of choice (strings with names of months- January, February, March..., December)

    'Year' column, single line of text (e.g. 2012, 2013, 2014)

    1. I need to create a calculated column which will return combined value of the columns above, but in DATE format e.g. 'Sep-2013' or '01-Sep-2013'.

    I then use that newly created calculated column to do this: http://iwillsharemypoint.blogspot.in/2012/03/sharepoint-list-view-of-current-month.html

    I am rubbish with formulas, can I have some help with my problem please?

    Wednesday, November 5, 2014 9:15 AM

Answers

  • Hi,

    Use the formula, I have tested by creating Months column with choice, Year column as numeric and Calculated column as DateTime returned.

    =DATE(Year,IF([Months]="January", 1,IF([Months]="February",2,IF([Months]="March",3,IF([Months]="April",4,IF([Months]="May",5,IF([Months]="June",6,IF([Months]="July",7,IF([Months]="August",8,IF([Months]="September",9,IF([Months]="October",10,IF([Months]="November",11,12))))))))))),1)

    Before applying the formula few things need to be noted.

    • DATE(YEAR, MONTH,DAY) will accepts three parameters all should be type numeric(integer).
    • Create the Year column of type numeric
    • Create the calculated column of type "DateTime" to return as date

    Please mark it answered, if your problem resolved or helpful.

    • Marked as answer by Lukas_UK Wednesday, November 5, 2014 2:34 PM
    Wednesday, November 5, 2014 10:17 AM

All replies

  • Hi Lukas,

    Please use the below formula

    =LEFT([Invoice Month],3)&"-"&TEXT([Year])

    This formula will generate output as Sep-2012

    Hoping this will solve your problem.

    Regards,
    Brij K


    • Edited by Brij K Wednesday, November 5, 2014 10:06 AM
    Wednesday, November 5, 2014 10:05 AM
  • Hi,

    Use the formula, I have tested by creating Months column with choice, Year column as numeric and Calculated column as DateTime returned.

    =DATE(Year,IF([Months]="January", 1,IF([Months]="February",2,IF([Months]="March",3,IF([Months]="April",4,IF([Months]="May",5,IF([Months]="June",6,IF([Months]="July",7,IF([Months]="August",8,IF([Months]="September",9,IF([Months]="October",10,IF([Months]="November",11,12))))))))))),1)

    Before applying the formula few things need to be noted.

    • DATE(YEAR, MONTH,DAY) will accepts three parameters all should be type numeric(integer).
    • Create the Year column of type numeric
    • Create the calculated column of type "DateTime" to return as date

    Please mark it answered, if your problem resolved or helpful.

    • Marked as answer by Lukas_UK Wednesday, November 5, 2014 2:34 PM
    Wednesday, November 5, 2014 10:17 AM
  • Thanks Mokhtar!
    • Edited by Lukas_UK Wednesday, November 5, 2014 2:36 PM
    Wednesday, November 5, 2014 2:35 PM