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…


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:

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.

Get the VBA Toolbelt!

Quit digging through old projects and forums like a chump! Download the VBA Toolbelt and start with the most common Excel tasks already done for you.

No spam, ever. Unsubscribe at any time. Powered by ConvertKit