PROBLEM:

I opened my Element Rank Estimator (Rank Calculator) spreadsheet and found that a number of the fields had reformatted to date. They looked fine the last time I had it open. It happens periodically and doesn't seem to relate to anything that I do.


SOLUTION:

This is a bug in certain installations of Excel. For an unknown reason, Excel adds the format "[$-409]d-mmm-yyyy h:mm AM/PM;@" and formats some cells that way.

 

To (hopefully) prevent the problem from happening again, try the following:

 

1. Go to Control Panel > Regional Settings and check that the regional settings are set correctly.

2. Repair Office on your computer.

 

To fix the problem in a particular calculator workbook, you can download a new Element Rank Estimator from http://www.natureserve.org/conservation-tools/conservation-rank-calculator and copy your data into it, or you can delete the "[$-409]d-mmm-yyyy h:mm AM/PM;@" custom format, using the detailed steps below. Neither option will prevent the problem from cropping up again, so I still recommend trying the 2 fixes above.

 

1. Unhide all hidden sheets in the workbook (right-click on any tab at the bottom and choose Unhide).

2. Unprotect ALL sheet in the workbook (Review tab in Ribbon), including the very first tab named “.” (the welcome page). (Leave password blank.)

3. Open the Format Cells dialog (one way is to click the little arrow in the bottom-right of the Number group in the Ribbon).

  

4. Go to the Number tab and choose the “Custom” Category.

5. Scroll down in the list of formats and highlight each format beginning [$-409] or anything else goofy like that. Click delete for each.

 

6. Your problem should be fixed in this workbook. Rehide the last 4 tabs in the workbook (white tab color) by right-clicking on the tab and choosing Hide. Save the workbook and close it. You do not need to reprotect all the sheets; that happens automatically when you open it the next time.

 

For more information see http://help.wugnet.com/office/Excel-2007-Cell-Format-Change-Bug-Warning-ftopict1143233.html or search the internet for "[$-409]d-mmm-yyyy h:mm AM/PM;@".