One of our academics wanted to calculate the difference between to columns of dates based on the year.

Easy I said, we’ll split the date columns so the years are in separate columns.  You can do a simple =A1-B1 to get your result.

It is almost that easy, there are however a couple of tricks (if someone has a more elegant solution please let me know).

  • First insert a column to the right of the column you are about to split
  • Click on Data
  • Select the column and click on Text to Columns
  • Select Fixed Width
  • Click on Next

split date1

  • Drag the line across to your desired separation pont
  • Click Next
  • On the next screen just click Finish

split date2

Here is the confusing bit.  When I split the  column is I found the year column had a full date in it.

split date4

To fix this

  • Select the dodgy year column,
  • Right click and select Format cells

split date5

  • Select Number and remove the decimal places
    You’ll notice that the Sample is showing the original year
  • Click OK and you’re done

splitdate6