Financial data usually contains dates; however, date formats are not standard between applications, worksheets, or countries. It is often necessary to make modifications to the date format in order to expedite analysis and adhere to local formatting standards.
In this recipe, you will learn to create an add-in that will allow you to quickly change the date format of a selection of dates within your financial data.
We will begin this recipe by adding code to the Excel worksheet:
1. In the worksheet that you wish to add the add-in, press Alt + F11 to open the visual basic editor (VBE).
2. Once open, from the toolbar choose Insert | Module, to insert a blank code module:
3. Enter the following code:
Sub DateConvert() Dim rDate As Range Dim dFormat As String Dim iCell As Range Set rDate = Selection dFormat = InputBox(—Enter the new Date format—, —Date Format—) For Each iCell In rDate iCell.NumberFormat = dFormat Next iCell End Sub
4. Save your work and...