View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld Ron Rosenfeld is offline
external usenet poster
 
Posts: 5,651
Default How can I insert a colon into a column of existing numbers

On Thu, 7 Jun 2007 09:00:00 -0700, jcmonzon
wrote:

I have two columns of numbers which are suppose to represent times, but were
entered as a number format. Example: "1845" instead of 18:45. I'm looking
for a way to insert the colon into the numbers so I can do a time calculation.

Note: formating the columns as Time does not work because of the way Excel
handles date & time in a serial manner.

Thanks


One way to convert to time:

=--TEXT(A1,"00\:00")

Format as Time

Then do a Edit/Copy Paste special/Values in place.
--ron