Automatically change year in Excel in available dates

Subhan_89

New Member
Joined
May 30, 2016
Messages
1
I am working on a daily stocks data that have dates that range from 2016 to 2000. The problem is the the data I have imported have converted all the years to "2016". The dates are in descending order. When the dates move from Januray to the next year's december, the year does not change.
Currently I am using date function. But I have to manually change the formula to the next year whenever the year changes. I want to automate this process as I have to work on alot of data that has this same issue.
Things to keep in mind


  1. Dates are in descending order from april 2016 to january 2000
  2. As the data is of daily stock prices, months in the data does not necesarily starts from 1st or ends at 31st. E.g one company's last date of december data was 14th in 2010.
  3. I want the dates and month to remaing same but the year to change whenever the ending date (not always 31st or 30th) of december comes.
Your help will save me alot of time and I will be grateful Thank You in advane
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

mole999

Well-known Member
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Do it right once,go to 31/12/2015 and in the next column, reduce by 365 i.e date - 365, double click to fill down, you only need to drop down 14 times and remember leap years, then all your new values, copy paste as value, which will give you a number, format that as date, and copy over the originals, then tidy up (do all that on a back up copy)
 

Forum statistics

Threads
1,143,654
Messages
5,720,109
Members
422,266
Latest member
Mattyw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top