1
I have a column with dates in the following format, a timestamp "5/29/2014 12:00:00 AM" -- I've tried all sorts of things to try to get all of them to trim the time part and end up with only "5/29/2014" to no avail.
Can anybody help?
Justin L.
Posted 2015-12-17T20:51:44.987
Reputation: 121
1
In Excel 2013 (maybe other versions), it seems that when you apply a format to a range of cells that already contain data that you want to be formatted, you need to do a little hack to force the cells with data to reformat. This post explained the hack (it describes a specific number format, but I believe it works for any formatting):
groovypost.com/howto/refresh-cell-data-number-formatting-excel
For ease, I've summarized below:
Et voila! The cells should now be formatted as you intended them to be.
Justin L.
Posted 2015-12-17T20:51:44.987
Reputation: 121
1
1) Highlight the cells you'd like to change the format on.
2) Right click the highlighted selection. Select "Format Cells".
3) In the dialogue menu, select the "Number" tab, then the "Date" option on the left.
4) I think the format you're looking for is the second from the bottom in the list on the right.
Scott Marlin
Posted 2015-12-17T20:51:44.987
Reputation: 104
Nothing seems to happen when I do this to the thousand or so cells that already contain dates in the first format. I figure I can write a little vb script but I was trying to avoid doing that – Justin L. – 2015-12-17T21:19:18.500
I noticed that when I click into a given cell then somewhere else, it goes to the right format... but I don't want to have to do this for thousands of rows. – Justin L. – 2015-12-17T21:26:10.277
After applying the format you want, can you save, close and reopen? It may be "stuck" updating the view, but the workbook knows the correct format. Forcing it to reopen should force it to adjust to the new format for you. It sounds like it has the correct format by the way it updates when you click off of the cell, but because there are so many, it can't process them all while it is open. – Scott Marlin – 2015-12-17T21:28:58.310
Closing and reopening didn't work either :( However, this blog post solved it! http://www.groovypost.com/howto/refresh-cell-data-number-formatting-excel/ Thanks for the help, Scott!
– Justin L. – 2015-12-17T21:29:32.803Awesome! Glad you found a solution. – Scott Marlin – 2015-12-17T21:37:11.087
@JustinL. you should make that an answer to this question. You can always answer your own question. – Ayan – 2015-12-18T02:02:39.340
What have you tried specifically? Have you tried to format the cells as date (selecting the MM/DD/YYYY format string)? – Dmitry Grigoryev – 2015-12-17T22:25:48.183