Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date format error in Excel
I have a spreadsheet that has about 8000 records. It was working fine until I
started using some functions like (=trim) and (=clean). Now all my date colums will not change from displaying the dates as numbers back to the proper date format. The cell displays 38908 but the function field shows it as 10/07/2006. I have formatted the cell every which way possible but it does not change. Any suggestions? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date format error in Excel
Richy,
Format the cells as date, then in a blank cell, enter a 1, copy it, select all your date cells, choose Edit / Pastespecial.... Check "Values" and "Multiply" and click OK. HTH, Bernie MS Excel MVP "Richy Rich" <Richy wrote in message ... I have a spreadsheet that has about 8000 records. It was working fine until I started using some functions like (=trim) and (=clean). Now all my date colums will not change from displaying the dates as numbers back to the proper date format. The cell displays 38908 but the function field shows it as 10/07/2006. I have formatted the cell every which way possible but it does not change. Any suggestions? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date format error in Excel
=TEXT(CLEAN(TRIM(A1)),"mm/dd/yyyy")+0
"Richy Rich" wrote: I have a spreadsheet that has about 8000 records. It was working fine until I started using some functions like (=trim) and (=clean). Now all my date colums will not change from displaying the dates as numbers back to the proper date format. The cell displays 38908 but the function field shows it as 10/07/2006. I have formatted the cell every which way possible but it does not change. Any suggestions? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date format error in Excel
Thanks it probably would have worked but I ended up creating a new worksheet
and formatting all my date and time fields then copyig the columns over and selecting "keep destination format". That seems to do the trick. Hope it stays that way, I now concerned with what caused the change "Bernie Deitrick" wrote: Richy, Format the cells as date, then in a blank cell, enter a 1, copy it, select all your date cells, choose Edit / Pastespecial.... Check "Values" and "Multiply" and click OK. HTH, Bernie MS Excel MVP "Richy Rich" <Richy wrote in message ... I have a spreadsheet that has about 8000 records. It was working fine until I started using some functions like (=trim) and (=clean). Now all my date colums will not change from displaying the dates as numbers back to the proper date format. The cell displays 38908 but the function field shows it as 10/07/2006. I have formatted the cell every which way possible but it does not change. Any suggestions? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date format error in Excel
Thanks for your help but I couldn't get this one to work it gave me an error
due to the +0 at the end. "Teethless mama" wrote: =TEXT(CLEAN(TRIM(A1)),"mm/dd/yyyy")+0 "Richy Rich" wrote: I have a spreadsheet that has about 8000 records. It was working fine until I started using some functions like (=trim) and (=clean). Now all my date colums will not change from displaying the dates as numbers back to the proper date format. The cell displays 38908 but the function field shows it as 10/07/2006. I have formatted the cell every which way possible but it does not change. Any suggestions? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date format error in Excel
Richy,
TRIM returns a string. Use =VALUE(TRIM(....)) in the future, and then your formatting will work. HTH, Bernie MS Excel MVP "Richy Rich" wrote in message ... Thanks it probably would have worked but I ended up creating a new worksheet and formatting all my date and time fields then copyig the columns over and selecting "keep destination format". That seems to do the trick. Hope it stays that way, I now concerned with what caused the change "Bernie Deitrick" wrote: Richy, Format the cells as date, then in a blank cell, enter a 1, copy it, select all your date cells, choose Edit / Pastespecial.... Check "Values" and "Multiply" and click OK. HTH, Bernie MS Excel MVP "Richy Rich" <Richy wrote in message ... I have a spreadsheet that has about 8000 records. It was working fine until I started using some functions like (=trim) and (=clean). Now all my date colums will not change from displaying the dates as numbers back to the proper date format. The cell displays 38908 but the function field shows it as 10/07/2006. I have formatted the cell every which way possible but it does not change. Any suggestions? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date format error in Excel
+0 or *1 at the end it converts the text date to a real date
"Richy Rich" wrote: Thanks for your help but I couldn't get this one to work it gave me an error due to the +0 at the end. "Teethless mama" wrote: =TEXT(CLEAN(TRIM(A1)),"mm/dd/yyyy")+0 "Richy Rich" wrote: I have a spreadsheet that has about 8000 records. It was working fine until I started using some functions like (=trim) and (=clean). Now all my date colums will not change from displaying the dates as numbers back to the proper date format. The cell displays 38908 but the function field shows it as 10/07/2006. I have formatted the cell every which way possible but it does not change. Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Date Format Problem | Excel Discussion (Misc queries) | |||
Excel date format issue | Excel Discussion (Misc queries) | |||
How to format date cells, Excel template Project scorecard matrix | Excel Worksheet Functions | |||
Excel: I enter date and format for date, but shows as number | Excel Discussion (Misc queries) | |||
Excel Query Wizard Date Format | Excel Discussion (Misc queries) |