Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
How do I keep custom formats in Excel 2000?
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.
Somehow the situation resolved itself, although I wasted a lot of time playing with these spreadsheets, adding duplicate date columns that were text formatted and hiding the actual date columns used in lookups. I guess at some point after adding back my custom date format to some cells, it took hold and the spreadsheets were able to reference the 'mm/dd/yyyy" date format. So then I restored the changed spreadsheets from my backup from the old PC, and they had the correct format.
Garbo, thanks for your help but I didn't need to try that. I'm the only user, as these are mainly spreadsheets related to my retirement accounts and other financial matters. If I were to die or fall ill and my brother needed to look at these and was on another computer, I don't think he'd care if the dates got reformatted. Being that I'm the only one who uses these or cares, and I know for future reference that I should be able to fix the formatting if I copy to my laptop, this is now ok.
1 Answer
- garbo7441Lv 77 years agoFavorite 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'.
