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?

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?

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?

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?

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?

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?

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?