Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
In History sheet (tracking changes), date (in column B) is 38,666 (instead of 11/10/2005)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
In History sheet (tracking changes), date (in column B) is 38,666 (instead of 11/10/2005)
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. -- Regards, Tom Ogilvy wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
In History sheet (tracking changes), date (in column B) is 38,666 (instead of 11/10/2005)
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
In History sheet (tracking changes), date (in column B) is 38,
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. " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tracking history of Office files chronologically | Excel Discussion (Misc queries) | |||
Please...need help setting up loss run history for claims tracking | Excel Discussion (Misc queries) | |||
Tracking inventory order history | Excel Discussion (Misc queries) | |||
Convert alphanumerical(eg. 12.11.2005) to date as 12-Nov-2005 in e | Excel Discussion (Misc queries) | |||
chnage date from tuesday 4 January 2005 10:39.26 to 04/01/2005 | Excel Worksheet Functions |