Date prob in excel 2000

N

News

I have a workbook with a far amount of data in it that is quite old ie. 6
yrs.

Tow problems

1. One of the office girls decided to clean her keyboard while the file was
open and now two columns of dates are shown as their serial number. I have
tried formatting the cell to general and back to date with no luck. the date
shows correctly in the formula bar but incorrectly i.e.. as its serial in
the cell. most annoying.

How do I get the correct view back in the cell.


2. Is there some way of "cleaning the database" to tidy up any guff that
might be in the file. All of our drafting and surveying software has clean
database and rebuild indexes and the such, is there anything like this for
the Excel files.


Thanks for any help.


--
Dan Field
(Surveyor)

email:
dfield at owrsurv dot com dot au
 
D

Dave Peterson

#1. Tools|Options|View Tab|Uncheck formulas

The shortcut for this setting is ctrl-` (control-backquote, to the left of the
1/! on my USA keyboard. One of the office women may have hit that key combo
while cleaning.

#2. Nope.
 
M

Martin Fishlock

Hi Dan:

1. Date problem:

I assume that you don't have an historical copy of the file (weekly backup)?

As you have tried formatting the date already then it could be conditional
formatting but I don't exprect so.

On way is to open a new sheet in a new workbook, copy/paste special values
the two columns to the new sheet get them formatted as required.

It may mean trying find replace '/' with '/' which may work then

Finally, in the old workbook delete the two columns with the dates then
insert new columns and then copy the columns from the new worksheet back.

2. Database cleanup.

Excel does not have built in facilities for cleaing files. Excel is an
aplication that allows user to create their own tabulations and therefore it
is the user's responsibility to ensure consistency.

There are ways to check data generally with match and vlookup but it user
initiated.

The difference with your surveying software is that is a special application
that has been developed for your company or purchased specially to do a job.

The main way that Excel is controlled if it is used for core critical tasks
is to ensure that the excel process/file is documented in your procedures and
update processes is part of the routine operation for the person assigned to
maintain the specific data.
 
Top