Converting Numbers like YYYYMMDD to Dates with VBA

What should you do when you get numbers (like YYYYMMDD) instead of dates in your data file?

Do you remember that time your data source was absolutely perfect? It was amazing — you dropped those rows straight into Excel and boom, everything was humming along…

EXCEPT THAT NEVER HAPPENED. NOT EVER. NOT EVEN ONCE.

Broken dates are at the top of the Analyst shit list. If your dates are coming in as numbers formatted YYYYMMDD (like 20150610), we’re going to fix them for good with the function in the GIF above.

Here’s the code:

Here’s a link to the code above so you can review it side-by-side with the walk through below. Right-click, “Open in new window”.

The beauty of a Public Function like the above is that you can call it straight from the worksheet, like any other formula! Your fellow Analysts will love you for it.

Remember the 4-step VBA process?

Step 1 – Setup
Step 2 – Exploration
Step 3 – Execution
Step 4 – Cleanup

Even though this Function only has 3 lines, all 4 steps are present. Let’s break it down:

Line 3 is our Step 1 – Setup — all we need a single variable.

Line 6 is our Step 2 – Exploration. We take the .Text of the Range that was passed in and assign it to our single variable.

Line 9 is handles both the Step 3 – Execution and Step 4 – Cleanup. There’s a lot going on here, so let’s go piece-by-piece:

• DateSerial takes a year, month and day and converts them into an Excel Date
• Left is a straightforward text-slicer, which takes a String the numbers of characters to slice.
• Mid is a lot like Left except it takes one extra parameter. First, you pass in a String, then the number of characters in (from the left) that you’d like the slice to start, and finally the number of characters.
• Right is EXACTLY like Left, except it starts from the right haha.

By shuffling the order of Left, Mid and Right, you can convert any nasty date number garbage that comes your way.

And finally, Step 4 – Cleanup is that last assignment. When you want your Function to return something in VBA, you need to set the Function name equal to the output, like Line 9 does.

And that’s it — numerical dates can now be converted to Excel Dates by simply calling =CONVERT_YYYYMMDD_TO_DATE from a cell, like you would call =SUM or =AVERAGE. Nice!

Is your number-to-date function running like a champ? If not, let me know and I’ll help you get what you need! And if you’d like more step-by-step, no-bullshit VBA guides delivered direct to your inbox, join my email newsletter below.