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.
VBA Code to check if folder exist Validation is one of the important parts of any programming language. As per few studies, 60% of the code is focused on validating input or output. In this…
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 Delete All Shapes On A Excel sheet Here is a VBA code which deletes all the shapes from an Excel sheet. Code is simple but you have to be bit careful while…
VBA Code to Filter Data in Excel Here is an easy reference code which filters data in the sheet. In the code, we have filtered the data in three steps. Step 1: Remove existing filter from…
This Excel VBA Code helps to Get User Name. Here is an example environ(username) or Application.username.This macro gets the username from active directory.
VBA Code to Find Last used Column or Row in Excel Sometimes as a developer, you need to take actions in Excel sheets based on last row or column. In Excel, there are two kinds…