Replace numbers with time

[nantucketbob]

I downloaded "sunrise/sunset" data into an Excel 2003 spreadhseet but the
times came as numbers, like "1622". I want to convert that to "16:22" but
the format cell option does not work. It converts it to a date and a time
(noon). Since the times change every few cells, search and replace is
inefficient. I tried searching for 16** and replacing them with 16:** but
Excel took that liberally. Is there a wildcard command to replace these
numbers: 1622, 1623, 1624 with 16:22, 16:23, 16:24?

 

[Niek Otten]

=TIME(INT(A1/100),MOD(A1,100),0)

 

[Stefi]

One way is to transform those numbers to Excel time values with this formula
in another cell (1622 being in A1):

=TIMEVALUE(LEFT(A1,2)&":"&RIGHT(A1,2))

Format the result cells like time!

If necessary, you can overwrite the original numbers with the time values
via Copy/PasteSpecial-Values.


--
Regards!
Stefi



„nantucketbob†ezt írta:

 

[Dave Peterson]

One more...

if 16:22 means 16 hours, 22 minutes, 0 seconds:
=--text(a1,"00\:00")

if 16:22 means 0 hours, 16 minutes, 22 seconds:
=--text(a1,"00\:00\:00")

And format the cell as a time.

The first minus coerces the text value into a number, but it's a negative
number. The second minus changes the sign back to positive/non-negative.