locked
Extract "Year" from "Date" column RRS feed

  • Question

  • I created a Date column in wss3 and want to extract the Year as text. I created a "calculated" Year field and used
    =Year([Date]). This forumula works well if "Date" is a text string but in my case it is not. It is a "date" column.

    TIA,

    Barkingdog

    Note:  I created a date column,  added the date 12/15/2010, used the formula "= YEAR(date)", which returned 1899!

    • Edited by edm2 Tuesday, February 9, 2010 7:34 PM update info
    Tuesday, February 9, 2010 7:00 PM

Answers

  • I did the same as you described, and it worked.

    Added a Date Time Column named (ADate) and then entered some test dates, including 12/15/2010.
    Added a calculated column with   =YEAR(ADate)   and it displayed the year (but with the dreaded commas)
    Edited the formula to get rid of the commas:   ="" & YEAR(ADate)

    I went back and tested with a column named "date" and that worked also.

    I did find a way to get "1899". Leave the date column empty.
    To fix this:

      =IF(ADate="","",YEAR(ADate))
    or
      =IF(ADate="","",""&YEAR(ADate))    to avoid the commas


    Mike Smith TechTrainingNotes.blogspot.com
    • Marked as answer by edm2 Tuesday, February 9, 2010 11:25 PM
    Tuesday, February 9, 2010 8:00 PM

All replies

  • I did the same as you described, and it worked.

    Added a Date Time Column named (ADate) and then entered some test dates, including 12/15/2010.
    Added a calculated column with   =YEAR(ADate)   and it displayed the year (but with the dreaded commas)
    Edited the formula to get rid of the commas:   ="" & YEAR(ADate)

    I went back and tested with a column named "date" and that worked also.

    I did find a way to get "1899". Leave the date column empty.
    To fix this:

      =IF(ADate="","",YEAR(ADate))
    or
      =IF(ADate="","",""&YEAR(ADate))    to avoid the commas


    Mike Smith TechTrainingNotes.blogspot.com
    • Marked as answer by edm2 Tuesday, February 9, 2010 11:25 PM
    Tuesday, February 9, 2010 8:00 PM
  • This is an old thread, but it always comes up when I search stuff like this, so I thought I would share what I use, which is:

    =TEXT([Date],"yyyy")

    Never gives me any commas. Works great.

    • Proposed as answer by camdencyclist Monday, February 8, 2016 3:29 PM
    Monday, February 8, 2016 3:20 PM
  • This worked like a gem for me, made my day.

    Thanks for posting.

    Monday, February 27, 2017 1:30 PM
  • Pour les français qui utilisent SharePoint en français la formule correcte est :

    =TEXTE([Date et Heure];"yyyy")

    où Date et heure est mon champ à convertir

    attention donc ; et non ,

    Thursday, February 7, 2019 10:38 AM