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
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 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…
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…
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…
VBA Code to Read Outlook Emails Reading emails from Outlook and capture them in Excel file is very common activity being performed in office environment. Doing this activity manually every time is quite boring and…
Custom Calendar Control for MS Access MS Access by default provides inbuilt functionality to pick dates using calendar control; however it lacks few basic functionalities which makes selecting a date bit difficult. For example, if…
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…