Changing dates using Excel

When you download information from databases to use for research it may sometimes be necessary to calculate new dates based on the original dates. An example is event studies where you want to do research within certain time periods. Excel considers dates to be numbers: number 1 stands for 1-1-1900 12:00:00 AM. As dates are numbers you can, for instance, add or substract a certain number of days using numbers. I used an example of this for event studies when in previous posts I explained how to use the Datastream tool Request Table.

Changing dates on a larger scale this way is possible too but it is not as easy because you would have to calculate how many numbers are necessary for calendar months (28-29, 30 or 31) or calendar years (365). Another method is using Excel to extract the part of the date you want to change and then recombine the old and new parts into a new date.

  1. If you have a large dataset with many dates and you want to calculate a date which lies exactly two years further in time. Create column B with only the years of the original date using the function =Year()
  2. Create column C and Add 2 to the years in column B to create new years using =Cell+2
  3. Now create columns D and E with the Months and days of the original date using the functions =Month() and =Day()
  4. Create new dates in column F using the function =DATE(C2;D2;E2)

I did a test using a date from a leap year 2004, specifically the 29th of February, and Excel correctly translated this date to 1st of March 2006. It looks like excel knows what to do in these cases. Unfortunately, when I tested changing 1-3-2002 it did not get me 29th of February 2004! It may be a good idea to check the dataset if you expect that these dates may cause problems. Depending on how exact the dates need to be for research you can, of course, always add x times 365 days to calculate dates too =Cell+(365*2). Example:


%d bloggers like this: