How do I keep custom formats in Excel 2000?

Question

I have a lot of spreadsheets where I custom format the dates as mm/dd/yyyy.  This is not one of the custom formats that comes as a default with Excel 2000, you have to add it.

I purchased a new PC and re-installed Excel 2000.  When I open the Excel files that I copied over from my old PC, all of the custom date formatting is lost.  My custom date format of mm/dd/yyyy is not in the list of custom format options.  All of my dates are now formatted as m/d/yyyy.  If I want to fix my dates, I have to add back the custom format for each file, then reformat all of the dates.  And I guess if I want to use these files on a different PC I'll have the same problem.

Is there any way to have my dates in the desired format without having to go through all of my spreadsheets and reformatting all the dates?  If necessary, I can go back to my old PC and recopy the Excel files.

garbo7441 · Accepted Answer

As the custom format you have added to your workbook is not in the table of custom formats in other users, Excel reverts to the 'nearest' format, in its opinion.

Therefore, you would need to use VBA routine make the workbook function as you wish for other users.

Copy the following two event handlers to the clipboard (highlight both codes entirely, right click inside the highlighted area, and 'Copy'):

Private Sub Workbook_Open()
If Sheets(1).Range("IV1").Value = "" Then
For Each ws In Sheets
For Each cell In ws.UsedRange
If IsDate(cell.Value) Then
cell.NumberFormat = "mm/dd/yyyy"
End If
Next cell
Next ws
Sheets(1).Range("IV1").Value = "X"
Sheets(1).Range("IV:IV"). EntireColumn.Hidden = True
End If
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
If IsDate(Target.Value) Then
Target.NumberFormat = "mm/dd/yyyy"
End If
End Sub

Press ALT + F11 (Function Key F11)

Double click 'This Workbook' in the Microsoft Excel VBA Project Objects in the upper left quadrant.

Paste both event handlers into the white editing area to the right (right click inside the area and 'Paste').

Close the VBE (Red button - top right).

Save and close the workbook.

Upon reopening it, the Open event handler will loop through all sheets and format any cell containing a date as 'mm/dd/yyyy'.  This event handler will only run once.  Thereafter, when the workbook is opened, it will not fire.

This leaves the issue of 'new dates' being added to any worksheet.  The Change event handler will evaluate each cell in which a data change occurs.  If the entry is a date, the cell will be formatted as 'mm/dd/yyyy'.

How do I keep custom formats in Excel 2000?

Trending Questions