5

I have a list of start times that need to be converted to 24 hour format. I changed the format for the entire column, but i have to press Enter for every row for excel to apply the new format.

Is there a was i can force excel to evaluate the cell format and change it. I have few thousand rows.

enter image description here

Improve this question
1
  • I can't replicate the problem in Excel 2007. If I highlight the range and change the format, all highlighted entries are changed.
    – fixer1234
    Nov 17, 2014 at 16:57

3 Answers 3

Reset to default
10

Yes, it bugs me too.

dim c as range
for each c in application.intersect(activesheet.usedrange, activesheet.columns("C"))
  c.value = c.value
next
Improve this answer
3
  • that worked great!
    – vpets
    Jun 20, 2011 at 1:30
  • 1
    you're right, none of the usual methods work.
    – Lance Roberts
    Jun 20, 2011 at 23:43
  • did not work for me. Excel 16.60 on mac
    – Michael Dausmann
    May 9, 2022 at 8:00
2

If all your cells have some text in common, you can also do a find/replace - in your case find every ":", and replace it with a ":" (i.e. find a way to find/replace that does nothing), and excel will re-format the cell.

Improve this answer
0
0

Another workaround is:

  1. Put value 1 in any unused cell on the sheet
  2. Copy the cell
  3. Select the range you wish to force-evaluate
  4. Past special - Values + Multiply
Improve this answer

You must log in to answer this question.