none
Calculated column to concatenate text and a year into a date RRS feed

  • Question

  • I have a column called YEAR which is a single line of text.  I need to create a calculated column called ARCHIVE DATE which puts together the constant date of July 1 with the contents of the YEAR field.  For example, if the YEAR field says 2018, then the ARCHIVE DATE field should be 07-01-2018.  The calculated field will need to be used in a VIEW that gets filtered by that archive date.

    I thought this would be easy but it's not working out how I planned.  I can get the value of the calculated ARCHIVE DATE field to look correct but the View is not showing the results. I tried filtering by "If ARCHIVE DATE is less than [today]" and then I tried "If ARCHIVE DATE is greater than [today]" but absolutely no items show.  I have set the data type returned as Date (date only).  I think that, although the field displays the correct information, SharePoint might not see it as a date it can evaluate.   

    How can I combine the hard date of July 1 with my year column into a new column which can be evaluated as a date for a View?

    Tuesday, August 20, 2019 6:33 PM

Answers

  • I tried using the below formula on the calculated column and it worked fine. I was able to filter without any issues on the calculated field.

    =DATE(YEAR,7,1)

    Please remember to mark the replies as answers if they help.

    • Marked as answer by mshehu Tuesday, August 20, 2019 9:55 PM
    Tuesday, August 20, 2019 8:24 PM

All replies

  • I tried using the below formula on the calculated column and it worked fine. I was able to filter without any issues on the calculated field.

    =DATE(YEAR,7,1)

    Please remember to mark the replies as answers if they help.

    • Marked as answer by mshehu Tuesday, August 20, 2019 9:55 PM
    Tuesday, August 20, 2019 8:24 PM
  • Hi Giridharan,

    Thanks for your sharing!

    Best Regards,

    Michael Han


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Wednesday, August 21, 2019 10:20 AM