ChatterBank1 min ago
Excel Problem
I have a column in Excel with the dates in the following format: 04/10/1984 00:00:00
How can I format these dates as 04/10/1984 in order to filter them. I have tried Format-Cells-Date but it doesn't work. If I copy and paste the date part to another column it works fine but with the length of this worksheet it would take all night. I just can't figure out how to get rid of the 00:00:00. Any help greatly appreciated.
How can I format these dates as 04/10/1984 in order to filter them. I have tried Format-Cells-Date but it doesn't work. If I copy and paste the date part to another column it works fine but with the length of this worksheet it would take all night. I just can't figure out how to get rid of the 00:00:00. Any help greatly appreciated.
Answers
1 to 4 of 4
Best Answer
No best answer has yet been selected by spotit3. Once a best answer has been selected, it will be shown here.
For more on marking an answer as the "Best Answer", please visit our FAQ.
Hello,
It may be your column is incorrectly formatted for some reason thus not allowing the changes.
You can try going to format, custom and then select dd/mm/yyyy from the list and see if that works.
Or you could copy and paste the entire column by clicking on the letter at the top to highlight it all then paste in a new column. Format as you want it then insert column by where it should be, cut and paste the formatted dates into the new column, then delete the original column, so in effect the dates will be where they should just in a new column with the correct formatting.
I am not a techie expert though - these are just ideas from experience using it.
Good luck.
It may be your column is incorrectly formatted for some reason thus not allowing the changes.
You can try going to format, custom and then select dd/mm/yyyy from the list and see if that works.
Or you could copy and paste the entire column by clicking on the letter at the top to highlight it all then paste in a new column. Format as you want it then insert column by where it should be, cut and paste the formatted dates into the new column, then delete the original column, so in effect the dates will be where they should just in a new column with the correct formatting.
I am not a techie expert though - these are just ideas from experience using it.
Good luck.
coppertop is right ... it's bad formatting
but you could still filter as usual because the 00:00:00 just means that only the date info is being used
however it looks lumpy - to fix
all dates are in effect custom formats
so highlight the column | right click
select the custom option
and in "type" type some letters
d = 1-31
dd = 01-31
ddd = mon-sun
m - 1-12
mm = 01-12
y = 09
yyy=2009
/ = /
so in your case
dd/mm/yyy
07/04/2009
will give what you want
but you could still filter as usual because the 00:00:00 just means that only the date info is being used
however it looks lumpy - to fix
all dates are in effect custom formats
so highlight the column | right click
select the custom option
and in "type" type some letters
d = 1-31
dd = 01-31
ddd = mon-sun
m - 1-12
mm = 01-12
y = 09
yyy=2009
/ = /
so in your case
dd/mm/yyy
07/04/2009
will give what you want
double click on the date and time and type the new date and time.
you can change the format of date and time from the settings of the excel.
either you can save this excel file in different format so that you cannot change.
if this excel file is saved in the ms office2003 and you are trying to open this file in ms office2007 . it creates a problem.
so check the file format first than try to edit the file .
you can change the format of date and time from the settings of the excel.
either you can save this excel file in different format so that you cannot change.
if this excel file is saved in the ms office2003 and you are trying to open this file in ms office2007 . it creates a problem.
so check the file format first than try to edit the file .
1 to 4 of 4
