In different parts of the world, there are different languages spoken and written. With this, a VBA programmer also faces language related issues while writing a VBA program. Here is a common date related problem solved which converts a date written in MM.DD.YYYY format into Excel standard date.
'This function converts a date from MM.DD.YYYY to system date format
Public Function ConvertToDate(strInputDate As String) As Date
'Variable Declaration
Dim iMonth As Integer
Dim iDay As Integer
Dim lYear As Long
'
'Get month from first 2 digits
If IsNumeric(Left(strInputDate, InStr(1, strInputDate, ".") - 1)) = True Then
iMonth = Left(strInputDate, InStr(1, strInputDate, ".") - 1)
Else
ConvertToDate = Null
Exit Function
End If
'Get day from input date
If IsNumeric(Mid(strInputDate, InStr(1, strInputDate, ".") + 1, 2)) = True Then
iDay = Mid(strInputDate, InStr(1, strInputDate, ".") + 1, 2)
Else
ConvertToDate = Null
Exit Function
End If
'Get year from last 4 digits
If IsNumeric(Right(strInputDate, 4)) = True Then
lYear = Right(strInputDate, 4)
Else
ConvertToDate = Null
Exit Function
End If
'Create a complete date and return the value
ConvertToDate = CDate(iDay & "-" & MonthName(iMonth) & "-" & lYear)
'
End Function
1. Open an Excel file
2. Press Alt+F11
3. Insert a Module (Insert>Module) from menu bar
4. Paste the code in the module
5. Now write ConvertToDate formula in a cell and select the cell which contains a date in MM.DD.YYYY format
6. You may get the converted date in number format like below
7. To change the date format into the desired format, right-click on the cell and select ‘Format Cells…’
8. In the ‘Number’ tab, click on ‘Date’ category and select the format type you want to display (here I have selected DD-MMM-YYYY format)
9. Done, your date is converted. You can drag the formula to other cells as well
To help you practice this code, we have made this code available through practice file. Click on the below link to download the practice file.
How to Add Outlook Reference in Excel VBA? To automate Outlook based tasks from Excel you need to add Outlook Object Library (Microsoft Outlook XX.X Object Library) in Excel References. You can follow below steps…
Excel Add-in helps you to extend the features of Excel application. Using Excel Add-in, you can perform custom actions in Excel such as formatting the data, doing complex calculations which are not possible through Excel formulas, Reading or Writing data in other Excel files and so many more actions.
VBA to Browse Outlook Folder Outlook is most commonly used emailing application used in the world. Many people spend their entire day on Outlook applications to read and respond to emails. To automate certain rule-based…
How to Export Access Data to Excel using VBA Code? Creating a VBA tool in MS Access is always better as compare to MS Excel. MS Access provides better user interface and ability to handle…
VBA Code To Change Cell Color Excel supports more than 16 million colors in a cell; hence you should know how to set the exact color in a cell. To do this, you can use…
Excel VBA Tool To Get File Properties Here is one more interesting VBA tool from the ExcelSirJi team. File Properties Tool is an Excel VBA tool that gets the following properties of the file. File…