How to Fix Dates in Microsoft Excel

I would never give dating advice, but I can tell you how to fix dates in Microsoft Excel. The question was posed in our ABBO (Accountants, Bookkeepers, and Business Owners) group on Facebook about converting bank statements to Excel files using PDF2XL. The issue presented is that the dates on the bank statement are formatted with only month and day. The statements were from 2014, however, absent the year info from the converted bank statements, Microsoft Excel is defaulting to 2015 for the year. This will screw up the transaction dates on an import into the accounting software.

One suggestion was to use Find and Replace, and this is an excellent suggestion. The potential danger is that you could find and replace the wrong thing. Based on this, I wanted to offer an alternative solution which doesn’t take any more time and guarantees the right result. Using a date formula, you can quickly insert a column, write the formula on the first date, then copy and paste it down. In less than 30 seconds you’ll have your solution.

When using a product like PDF2XL, remember that the goal is to get the data into Excel as quickly as possible, because Microsoft Excel already has powerful tools built in, which can get you where you need to be with your data quickly. At the same time you want to do as much as possible before the import so that the time you spend in Microsoft Excel is minimized, and limited to just the more sophisticated things, like the example here, of fixing the dates.

Unfortunately there is no formula for fixing your dating life, but a simple formula can fix your dates in Microsoft Excel. Watch the video, if you haven’t already, and please post your comments and questions below.

Share this Post

One Comment on “How to Fix Dates in Microsoft Excel”

  1. Seth, this worked perfectly. This will be a huge time saver for me. Thank you so much for taking the time to create this video!

How do you feel about it?

Your email address will not be published. Required fields are marked *