|
|
||||||
|
#1
02-09-10, 02:38 PM
|
|
|
|
|
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? |
|
|
|
#2
02-09-10, 02:54 PM
|
|
|
|
|
=TIME(INT(A1/100),MOD(A1,100),0)
|
|
#3
02-09-10, 02:58 PM
|
|
|
|
|
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. |
|
#4
02-09-10, 03:10 PM
|
|
|
|
|
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. nantucketbob wrote: [..] |
|
|
| Similar Threads | |
| Word 2007 - How to replace hardcoded numbers with list numbers? In previous versions of Word, I was able to take a list that had hard-coded numbers (e.g., from copying in from excel or a text file) and use either the 'bullets' or the... |
|
| take a sequence of numbers 2 at a time, print numbers and theirproduct? How would you take a sequence of numbers in groups of 2 and print out the numbers and their product? Here is my solution, but I'm sure some li5p h4x0r has a much more... |
|
| How to replace Arial numbers with David Numbers Hi, I have a strange problem concerning multi language support and hopefully you can help. I am editing a document that is all a David font document (David is a hebrew... |
|
| replace one set of numbers with another I want to be able to put in the code and it replace it with a phone number. Is there any way to do this? |
|
| How to Replace numbers and text with numbers only? I am using Excel 2003. I have a worksheet that has a column with addresses in the format of "123 Main Street" "345 South Street", etc. I want to break that data into two... |
|
|
All times are GMT. The time now is 01:49 PM. | Privacy Policy
|
Replace numbers with time