Getting Rid Of Zeros At End Of Date On Excel

[spotit3]

Hi

I have a column of dates on an Excel spreadsheet. They all end with 00.00.00
I have tried everything to format these cells so that just the date shows but have had no joy so far.

Can anyone out there help me please?

[2sp_]

Highlight the column that you want to format

Right click and go to Format Cells

Under Category you can select the type of figure you want to display

Then you can select the way you want the number to appear in the list that will appear just to the right

Or you can set a custom appearance

Hope that makes sense!

[2sp_]

Sorry, should have said to make sure you are on the Numbers tab once you have opened Format Cells

[spotit3]

Hi 2SP I have tried that but it doesn't work. With dates I input myself I have never had a problem formatting them.
Perhaps I should have said i didn't input these dates myself, they were already there on this sheet.
I vaguely remember somebody telling me to input =cellvalue and something else but can't remember the rest and no matter what I try I can't figure it out.

[aelmpvw]

do a search and replace?

[Old_Geezer]

Weird. What is actually typed in the cell ? It sounds like it is not written in the same "format/style" as dates are defined in your Excel settings. In which case you may need to work out how to do a conversion first.

[Old_Geezer]

It is full stops not colons is it ? Just wondered if it was showing as a time not a date.

The Value function would change text to a number, which may have been what you were told ?

[Old_Geezer]

Or there's a datevalue function to try.

[SeaJayPea]

Are you saying that they look like this:

21/05/2014 00:00:00?

If so, that's because somebody has set them to display date and time in the Number/Custom format. Should be able to change that fairly simply by taking it back to Date only.

[SeaJayPea]

Did you sort it?

[davewr]

Great answer CJP; funny how things can be so obvious when someone points them out. Pity no reponse from originator.

[spotit3]

Sorry I haven't been able to access the site sooner to reply. I have tried all the suggestions but still no joy. Thank you anyway to all those who tried to help and my apologies for not getting back to you sooner.

[SeaJayPea]

If you care to let me see the spreadsheet I'm sure we can work out a way of doing it. Do you have an email address you don't mind posting on here? I don't have a throwaway one myself, I'm afraid.

[spotit3]

Thanks SeaJayPea, I will try to do that next time I am on that PC.

[spotit3]

Hi SeaJayPea, Here is a throwaway email address for me.
[email protected]