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!
The 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.
"=2017-01-01 + 7" = 2017/01/08.
Let us assume they suggest it is not their problem.
The first thing we have to ascertain is whether the date is in USA or International 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.
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.
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.
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.
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