Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

migrated from Jun 20 '11 at 2:33

This question came from our site for professional and enthusiast programmers.

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 '14 at 16:57
up vote 7 down vote accepted

Yes, it bugs me too.

dim c as range
for each c in application.intersect(activesheet.usedrange, activesheet.columns("C"))
  c.value = c.value
share|improve this answer
that worked great! – vpets Jun 20 '11 at 1:30
you're right, none of the usual methods work. – Lance Roberts Jun 20 '11 at 23:43

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.