In History sheet (tracking changes), date (in column B) is 38,666 (instead of 11/10/2005)

[rmosko]

I'm using Excel 2003 SP2 under WinXP SP2, and I recently enabled "Track
Changes" - with changes listed in the "History" sheet - in one of my
spreadsheets. This is my first experience with Change Tracking in
Excel.

The problem: in the Date column of the History sheet, the dates are
displaying as numbers (for example, "38,666") instead of dates
(...should be "11/10/2005"). Since the sheet is protected, I can't
change the cell formats.

Has anyone else seen this? Is there a solution?

Rory Mosko

 

[Tom Ogilvy]

Changing the format to Date is the solution. Dates are stored as the number
of days from the 1900.

so 38666

?format(38666,"mmm dd, yyyy")
Nov 10, 2005

is as the date shows.

the solution is to get the sheet unprotected and get the cells formatted.

 

[rmosko]

Tom,

Thanks for the reply.

I should have given more information here, as your suggestion was the
first thing I tried to do.

When you ask Excel to track changes, you have the option to display all
changes made to the spreadsheet in a separate sheet that Excel tacks on
the end of your spreadsheet. That sheet is labeled "History", and it
provides a history of changes to your spreadsheet in a table with the
column headings "Action", "Date", "Time", "Who", "Change", "Sheet",
"Range", "Old Value", "New Value", etc. The sheet cannot be
unprotected (I've tried to do that), and so the number format cannot be
changed, and that's what my problem is... In a sheet created
entirely by Excel itself, how and WHY would it create columns called
"Date" and "Time" and 1) display the data in a number format that
renders them pretty much useless, and then 2) lock the format so as to
give you no way to fix it???

Rory

 

[Guest]

It's not the most elegant of solutions, but there is a brute force approach.

Simply create another sheet. Every cell on that sheet=the corresponding
cell on the history sheet, e.g. newsheet!A1=history!A1.

You can then play around with the newsheet contents to your heart's content.

As I say... It's not elegant.