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
- Drag the line across to your desired separation pont
- Click Next
- On the next screen just click Finish
Here is the confusing bit. When I split the column is I found the year column had a full date in it.
To fix this
- Select the dodgy year column,
- Right click and select Format cells
- Select Number and remove the decimal places
You’ll notice that the Sample is showing the original year
- Click OK and you’re done