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:
DateSerialtakes a year, month and day and converts them into an Excel
Leftis a straightforward text-slicer, which takes a
Stringthe numbers of characters to slice.
Midis a lot like
Leftexcept 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.
Rightis EXACTLY like
Left, except it starts from the right haha.
By shuffling the order of
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
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.