How to Fix Dates in Your Excel Worksheet with VBA

How often do we get dates in spreadsheets supplied to us as 12.26.2016, or 26/12/2016 (UK format), only to be told the date is invalid or there’s no month 26? This article explores fixing dates with VBA, using TRIM, LEFT, RIGHT and MID functions.

The article assumes the reader has the Developer ribbon displayed and is familiar with the VBA Editor. If not, please Google “Excel Developer Tab” or “Excel Code Window”.

The xlsm in this exercise can be downloaded here.

Not Our Problem!

Adding 7 Days To A DateThe best place to solve a problem is at source. However, no amount of persuasion can convince the payroll department in this case that 12.26.1994 is not a valid date (unless so configured in the computer’s Control Panel for some Eastern European countries).

In fact we can prove it is not machine-readable. For example, adding 7 days to a date:

"=01.01.2017 + 7" = #VALUE. 

"=2017.01.01 + 7" = #VALUE.

whereas…

"=2017-01-01 + 7" = 2017/01/08.

Let us assume they suggest it is not their problem.

Date Formats

The first thing we have to ascertain is whether the date is in USA or International format.Date In USA Format

Our example makes it clear we are looking at USA usage, i.e. MDY rather than international format DMY.

Once we have established the source, we need to change the data formats so that Excel can make sense of them, whether internationally or in the USA.

The best way to do this is to change the date to yyyymmdd, a format that needs no qualification.

The Process

We will cycle through each row in the document, calling a function to “correct” the date according to source country. Once the date is corrected, we will calculate the Employee’s age.

The Code

Copy the following code into a new module:

Option Explicit

Sub Main()
    Dim strNewFormat As String
    Dim strDate As String
    Sheets("Main").Range("B4").Select
    
    'Cycle through the sheet rows, using IDNumber as an anchor
    'to prevent a premature halt caused by a blank date of birth
    Do While ActiveCell > ""
        If ActiveCell.Offset(0, 2) > "" Then
            strDate = ActiveCell.Offset(0, 2)
            
            'Remove leading or trailing spaces
            strDate = Trim(strDate)
            
            'Call the function
            strNewFormat = ReformatDate(strDate, "USA")
            
            'Write the result from the function ReformatDate to a new column
            ActiveCell.Offset(0, 3) = strNewFormat
            
            'Determine age by subtracting the previous column from today's date
            ActiveCell.Offset(0, 4) = "=(NOW()-RC[-1])/365.25"
            
            'Convert to intger, thus lopping off decimal places
            ActiveCell.Offset(0, 4) = Int(ActiveCell.Offset(0, 4))
        End If
        Range("B" & ActiveCell.Row + 1).Select
    Loop
End Sub

Function ReformatDate(sDate As String, sSource As String)
    Dim yyyy, mm, dd As String
    yyyy = Right(sDate, 4)
    If sSource = "USA" Then
        mm = Left(sDate, 2)
        dd = Mid(sDate, 4, 2)
    Else
        mm = Mid(sDate, 4, 2)
        dd = Left(sDate, 2)
    End If
    ReformatDate = yyyy & "-" & mm & "-" & dd
End Function

Add a button to your form, and assign it to Sub Main.

Caveat

Before adding too much complex code to your module, be advised that Excel is not always stable in major application development, and often unable to recover the damaged code itself. The result could be the corruption of your only copy since the corruption happens in the “Save”.

Back up frequently and have the use of a tool to fix Excel file corruption.

Author Introduction:

Felix Hooker is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair rar file corruption and sql recovery software products. For more information visit www.datanumen.com

Leave a Reply

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