Former Tech Republic Download
I authored what I thought was a nice little date format converter for Excel several months ago using some VB and macros. Matter of fact, it was downloaded probably close to 12,000 times in one month.
Can't get it to work in Office XP. And short on time. Anyone have any ideas:
Reformat dates and add leading zeros to Excel values
Microsoft Excel offers a bevy of cell formatting options. Users can choose Currency or Accounting for monetary values, select a variety of date and time formats, use special formats for values such as ZIP codes and Social Security numbers, and even create custom formatting options. While these choices give Excel users a high degree of flexibility and are a plus, they can cause problems, particularly if you support users who routinely export data from Excel into applications that are less format-flexible.
Such is the case for TechRepublic member Rick Kuhn. Rick?s organization routinely exports data?names, addresses, dates, ZIP codes, Social Security numbers, and the like?from Excel into an in-house application that can accept dates only as text values in mmddyyyy format. Unfortunately, converting values from a date format to text in Excel isn?t as easy as clicking Format | Cells and changing the Category. To make matters worse, many of the original Excel files have Social Security numbers and ZIP codes formatted as General instead of Text and are therefore missing leading zeros.
To help his Excel users quickly reformat spreadsheets into a form that their in-house software can use, Rick created three VB scripts: one that converts date formats in mmddyyyy, one that adds leading zeros where necessary, and a final script that performs a copy, paste, and paste special values operation. To make the process as simple for his users as possible, Rick then created an Excel add-in, an Excel startup file, and three macros (i.e., hotkeys for the VB scripts). Rick?s Excel users now have a simple two-button fix for each problem.
This download contains all the components you need to provide a similar solution for your Excel users, including:
? Code for each VB script.
? Detailed instructions on how to install add-in and startup files.
? Detailed instructions on how to use the macros with the sample spreadsheet (Demo.xls).
? The Excel startup file?Personal.xls.
? The Excel add-in?Formatdates.xla.
? A sample spreadsheet (Demo.xls) with generic data that you can test.
You can also use the techniques outlined here to customize Rick?s scripts and macros to meet your users' specific needs.
VB scripts
Converts Date format into Text format (mmddyyyy)
Function FormatDate(d) As String
If IsDate(d) Then
FormatDate = Format(d, "mmddyyyy")
Else
FormatDate = "00000000"
End If
End Function
Sub bas()
End Sub
Macro Number 1
Sub Step1()
'
' Step1 Macro
'
'
ActiveCell.FormulaR1C1 = "=formatdate(RC[-1])"
Selection.Copy
End Sub
Macro Number 2
Sub Step2()
'
' Step2 Macro
'
'
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub
Installing the Format Dates, Leading Zeros, and CPSV buttons
Rick gives the following detailed instructions to his Excel users who want to take advantage of his handy formatting tools. Once you have developed your customized formatting tools, use these instructions as a template for creating your own set of directions.
Note: The file locations specified in steps 1 and 2 will depend on how your organization has installed Microsoft Office and Microsoft Windows. Windows 95 and 98 users are likely to find the Application Data directory under the C:\Windows directory. Windows 2000 and XP users will find the Application Data directory under C:\Documents and Settings\username\directory (where username is the name of a particular user). Also, you will need to configure Excel?s security settings to allow the execution of macros before you use these instructions.
1. Copy and paste the Personal.xls file into the following directory, as shown in Figure A:
C:\WINDOWS\Application Data\Microsoft\Excel\XLSTART
Figure A
2. Copy the Formatdates.xla file and paste it into the following directory, as shown in Figure B:
C:\WINDOWS\Application Data\Microsoft\AddIns
Figure B
3. Open a new, blank Excel workbook.
4. Click on Tools and then Add-Ins, as shown in Figure C.
Figure C
5. Check the box for the Formatdates add-in and click OK, as shown in Figure D.
Figure D
6. Right-click anywhere blank on the toolbar at the top of Excel and choose Customize, as shown in Figure E.
Figure E
7. Select the Commands tab, scroll to the bottom, and select Macros, as shown in Figure F.
Figure F
8. Click on the Custom Button option, drag it to the toolbar at the top in Excel, and then release. Repeat this process two more times, as shown in Figure G.
Figure G
9. Right-click on the first happy face to bring up a menu of options, as shown in Figure H.
Figure H
10. Type Format Dates into the Name text box and select Text Only (Always), as shown in Figure I.
Figure I
11. Assign the new hotkey the following macro by clicking on Assign Macro and clicking OK, as shown in
Figure J.
Figure J
12. Repeat the above procedure (steps 9-11) for the Leading Zeros and Copy/Paste/Paste Special Values (CPSV) functions, as shown in Figure K and Figure L.
Figure K
Figure L
13. Close the Customize box and you are finished! You should now have three new buttons on your Excel toolbar: Format Dates, Leading Zeros, and CPSV.
Using the Format Dates, Leading Zeros, and CPSV buttons
Note: These instructions use a sample spreadsheet (Demo.xls), assume that the user has already installed the Excel Startup file (Personal.xls) and the Excel Add-in (Formatdates.xla), and assume that the user has completed the previous set of instructions on installing the Format Dates, Leading Zeros, and CPSV buttons.
Also, because the macros rely on Excel?s ?concatenate? function, for the Leading Zeros function to work, the blank column used must be formatted as General and not as Text. In fact, no equation will work in columns formatted as Text.
1. Place your cursor in cell B2 and click the new Format Dates button on the Excel toolbar. The Date formatted value from cell A2 should be converted to a Text value in mmddyyyy format in cell B2, as shown in Figure M.
Figure M
2. Highlight the cells next to the values you want to convert, as shown in Figure N.
Figure N
3. Click the new CPSV button on the Excel toolbar, and column B will be formatted in mmddyyyy format and as Text to avoid missing leading zeros, as shown in Figure O.
Figure O
4. Repeat the above procedure to add missing leading zeros in columns containing such information as Social Security numbers, ZIP codes, and employee ID numbers, but use the Leading Zeros button instead of the Format Dates button.