I'm not sure where to start with this. I want to create a column in Excel that will essentially simplify another column. I have a column of dates with month, day, year, and a time. I want to use a formula to search for the year and then output that year into a new column. I am using Excel 2013 for this.

Something like IF(ISNUMBER(SEARCH("%2011%,A1)), "2011", "??").

Except I'd like the formula to be able to incorporate outputting 2011, 2012, 2013, 2014, 2015, or 2016 if that year is found instead.

Where do I start on this? is it even possible?

Thank you

  • is that column formatted as date? if yes, then you can simply use YEAR(), if not, do you have a fixed format? please post some sample data to help us understanding your issue. Apr 10 '17 at 14:21
  • The data wasn't in a date format but its easily converted into one. An example is: "06/25/11 12:00 AM." Edit: I didn't know of the year function..that worked perfect. Thank you! Can you post that as an answer so I can accept it? Apr 10 '17 at 14:29

If your data is stored as date (number) then you can use YEAR() function.

If it's stored as text, then DATEVALUE() function may help.

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.