Split dates into separate excel columns (more fiddly than it ought to be)

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


Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>