Why will my cells not format?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jamie Kelly - 12 Jan 2006 21:52 GMT

I have collected about 1000 points of data from the HyperTerminal program and
saved the data as two file formats one being .txt and the othet .csv. When I
load the .csv file I am not taken through the wizard which is okay. When I
load the .txt file I am. Anyways, my question is.. why will my cells not
format? Currently this is what the cell looks like that i want to format
"01/12/06 15:38:17" and there is one for every second for about 1000 seconds.
Excel reads these cells as general cells, I am curious why I can not change
them to a date format. The reason I want them to be a date format is because
I will be graphing this informat and it needs to be acurate as I am writing a
manual on how to setup a "Internal Data Logger for a portable D.O. analyzer"
and how to send the information to a computer. Any help?

Recap: Why will my date/time cell "01/12/06 15:38:17" not format to lets say
"Thursday, January 12, 2006?

I do believe the cells are locked.. but when I unlock them.. still nothing
happens.

Reply to this Message

Niek Otten - 12 Jan 2006 22:05 GMT

< Recap: Why will my date/time cell "01/12/06 15:38:17" not format to lets
say "Thursday, January 12, 2006? >

Because it is text, not a date (not for Excel).
Importing as txt gives you a chance to get the right data type (which is not
the same as the right format).
An alternative is to change it to a date afterwards with the DATEVALUE()
function. Not all date text formats are recognized by this function,
however. Maybe you have to (temporarily) change your Windows settings in the
Control Panel and/or strip time characters from the date.

Signature

Kind regards,

Niek Otten

>I have collected about 1000 points of data from the HyperTerminal program
>and
[quoted text clipped - 22 lines]
> I do believe the cells are locked.. but when I unlock them.. still nothing
> happens.

Reply to this Message

Dave Peterson - 12 Jan 2006 23:47 GMT

Another way to convert the text dates/times to real dates/times is to:
select the range
edit|replace
what: /
with: /
replace all.

If your cells are really what you posted, I think excel will see that the
entries are dates/times.

Then try format|cells|number tab to change the format.

> I have collected about 1000 points of data from the HyperTerminal program and
> saved the data as two file formats one being .txt and the othet .csv. When I
[quoted text clipped - 13 lines]
> I do believe the cells are locked.. but when I unlock them.. still nothing
> happens.

Signature

Dave Peterson

Reply to this Message

David McRitchie - 13 Jan 2006 18:41 GMT

and if neither of the two previous replies work, try this on a copy in
case the date format is not the same as yours for instance
if there is any question on interpretation that the date is mm/dd/yy or dd/mm/yy

Try the TrimALL macro
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

BTW, you can of course override the formatting so can't tell for sure
unless you test with ISTEXT or ISNUMBER, but if the date is
left aligned you would expect that it is text and not a number, and if
it is right aligned that it is a number.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Reply to this Message