0

How do I change the dates in Excel for the below scenario:

Existing dates:
2/2/2014
3/12/2014
1/1/2015
2/1/2015

Target converted dates:
3/31/2016
3/31/2016
1/1/2016
2/1/2016

I want all the dates with 2014 (no matter what month and day) year to be a standard date 3/31/2016 and all the dates with 2015 year to add an additional year to their exact date and month.

Note: There are other columns (such as Name and Phone number) pointing to these corresponding dates. I don't want to lose the current sorting of the Excel sheet.

Can someone help me with this?

  • As your text I want all the dates ... do you mean you want to filter your sheet rows or you want to use year in somewhere else like another function ? – shA.t Mar 11 '15 at 21:53
1

Excel provides functions do deal with dates like:

  • DATE which take the following parameters: DATE(year, month, day)
  • YEAR, MONTH and DAY which takes dates in parameters and return year, month and day of the given date.

Lets say that the dates you want to test are in the column A.

So, to test dates and classify them between 2014 and 2015, you can do:

IF(YEAR(A1)=2014,2014,IF(YEAR(A1)=2015,2015,NA()))

To generate the 3/31/2016 date, you do:

DATE(2016,3,31)

To add one year to the date which is in the column A, you do: DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))

And so the final formula is:

IF(YEAR(A1)=2014,DATE(2016,3,31),IF(YEAR(A1)=2015,DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)),NA()))

or

IF(YEAR(A1)=2014,DATE(2016,3,31),IF(YEAR(A1)=2015,DATE(2016,MONTH(A1),DAY(A1)),NA()))
  • As an alternative to DATE(2016,3,31), you could use DATEVALUE("3/31/2016"). – G-Man Mar 4 '15 at 17:40

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.