Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OFFICE 7
I copied a xls worksheet from an Excel 2003 to a new PC with Vista and Office
7. The worksheet has over 7000 rows, one of which contains dates. The dates have shown up a 5 digit numbers. I formatted the date column to show dates as 1/1/09 formats. Only 15% to 20% of the cells changed to the 1/1/09 format. The rest seemed to be locked in the 5 digit number format and can't be changed. I tried to delete a few cells and manually type in dates. The cells remained as 5 digit numbers. Any sugesstions would be appreciated. -- Bob Reddy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OFFICE 7
On Wed, 11 Nov 2009 19:03:02 -0800, Bob Reddy
wrote: I copied a xls worksheet from an Excel 2003 to a new PC with Vista and Office 7. The worksheet has over 7000 rows, one of which contains dates. The dates have shown up a 5 digit numbers. I formatted the date column to show dates as 1/1/09 formats. Only 15% to 20% of the cells changed to the 1/1/09 format. The rest seemed to be locked in the 5 digit number format and can't be changed. I tried to delete a few cells and manually type in dates. The cells remained as 5 digit numbers. Any sugesstions would be appreciated. Make sure the sheet is unprotected, and highlight the entire column when you perform the format operation. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OFFICE 7
The dates might have been saved improperly as text. Try filling out a
function in the next open column with this function =D2*1 (if the date is infact in d).. Fill this down and then copy this column over the top of the old one as values (paste special, values). What this does is take the date number "40000" and turn it into a number which excel can format as a date "mm/dd/yyyy". TFT "CellShocked" wrote: On Wed, 11 Nov 2009 19:03:02 -0800, Bob Reddy wrote: I copied a xls worksheet from an Excel 2003 to a new PC with Vista and Office 7. The worksheet has over 7000 rows, one of which contains dates. The dates have shown up a 5 digit numbers. I formatted the date column to show dates as 1/1/09 formats. Only 15% to 20% of the cells changed to the 1/1/09 format. The rest seemed to be locked in the 5 digit number format and can't be changed. I tried to delete a few cells and manually type in dates. The cells remained as 5 digit numbers. Any sugesstions would be appreciated. Make sure the sheet is unprotected, and highlight the entire column when you perform the format operation. . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
5 digit dates in Excel/Access
Hi Troy,
Thanks for posting your response - that was so helpful to me. I recently imported data from Excel into Access and the dates changed to 5-digit numbers. I had no idea how to sort it out and all the information online seems to relate to converting 6-digit numbers. Your tip really did the trick. Many thanks. R "TroyT" wrote: The dates might have been saved improperly as text. Try filling out a function in the next open column with this function =D2*1 (if the date is infact in d).. Fill this down and then copy this column over the top of the old one as values (paste special, values). What this does is take the date number "40000" and turn it into a number which excel can format as a date "mm/dd/yyyy". TFT "CellShocked" wrote: On Wed, 11 Nov 2009 19:03:02 -0800, Bob Reddy wrote: I copied a xls worksheet from an Excel 2003 to a new PC with Vista and Office 7. The worksheet has over 7000 rows, one of which contains dates. The dates have shown up a 5 digit numbers. I formatted the date column to show dates as 1/1/09 formats. Only 15% to 20% of the cells changed to the 1/1/09 format. The rest seemed to be locked in the 5 digit number format and can't be changed. I tried to delete a few cells and manually type in dates. The cells remained as 5 digit numbers. Any sugesstions would be appreciated. Make sure the sheet is unprotected, and highlight the entire column when you perform the format operation. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Install Office Standard XP on Vista. Uninstall Office 2007 Trial? | Excel Discussion (Misc queries) | |||
eula pops up every time i open an office program. office 2003 ins. | Excel Discussion (Misc queries) | |||
Office 2007 compatibility pack on Office 2003 (slow network file | Setting up and Configuration of Excel | |||
Office 2000/Office 2003 Excel not printing landscape vise versa | Setting up and Configuration of Excel | |||
Merging excel office data into a publisher office project(Australi | New Users to Excel |