how do I format cells to change date and time to just date

I want to format a column that contains date and time and I want it to show
just the date and not the time. Going into format and clicking on the date
and changing doesnt work.

Probably the cells aren't really dates, but text. You can check with the
ISTEXT() function.
If they are text, formatting doesn't have any effect.

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"bondam" <[email protected]> wrote in message
news:[email protected]...
>I want to format a column that contains date and time and I want it to show
> just the date and not the time. Going into format and clicking on the date
> and changing doesnt work.

if your column contains text (sounds like it does but you can verify by using ISTEXT or TYPE - Type will return "2" if it's text) then you can convert the values to a number by using VALUE. You have to insert a column first (ideally to the left). Let's say you have a date in the cell C2 and B is the new column you added. Type "=VALUE(C2)" in cell B2. That will convert your string date (as long as it's in a date format that Excel recognizes) to a number date. It will be formatted as a number initially but you can now apply the date formats. If this is a one time change, I would copy > paste values on column B and then delete column C so it's cleaner to work with.

Likely, your data is just text, then. You can try fixing it with "data isn't
recognized" at:

http://www.officearticles.com/excel/...soft_excel.htm

*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"bondam" <[email protected]> wrote in message
news:[email protected]...
> I want to format a column that contains date and time and I want it to
show
> just the date and not the time. Going into format and clicking on the date
> and changing doesnt work.