Home 
Search 
Today's Posts 
#1




How can I insert a colon into a column of existing numbers
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 
#2




How can I insert a colon into a column of existing numbers
If the numbers are always 4 digits you can use this:
=VALUE(LEFT(A1,2)&":"&RIGHT(A1,2)&":00") and copy down as required. However, I would expect you to have 3digit times (eg 930 to represent 9:30) and even 2digit times for those before 1:00 am, so a safer bet would be the following: =VALUE(LEFT(A1,LEN(A1)2)&":"&RIGHT(A1,2)&":00") and even this won't work if you have onedigit times, eg 5 to represent 00:05, or 5 minutes past midnight, so the following will cope with all these situations. =VALUE(INT(A1/100)&":"&MOD(A1,100)&":00") where A1 contains your value  adjust as necessary. Format the cell as time and then copy down. Hope this helps. Pete On Jun 7, 5:00 pm, 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 
#3




How can I insert a colon into a column of existing numbers
On Jun 7, 12:00 pm, 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 Try: =LEFT(A1,2) & ":" & RIGHT(A1,2) This puts a colon between the left two numbers and right two numbers Mike in Philadelphia 
#4




How can I insert a colon into a column of existing numbers
If 1845 is 00:18:45 (18 minutes, 45 seconds):
=TEXT(A1,"00\:00\:00") If 1845 is 18:45:00 (18 hours, 45 minutes): =TEXT(A1,"00\:00\:\0\0") Format each as time. 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  Dave Peterson 
#5




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 
#6




How can I insert a colon into a column of existing numbers
well theres a tedious 'quickfix' way out, if i may say
example: Time 1200 =LEFT(A2,2) =RIGHT(A2,2) =CONCATENATE(B2, ":", C2) 1820 =LEFT(A3,2) =RIGHT(A3,2) =CONCATENATE(B3, ":", C3) 1500 =LEFT(A4,2) =RIGHT(A4,2) =CONCATENATE(B4, ":", C4) 1730 =LEFT(A5,2) =RIGHT(A5,2) =CONCATENATE(B5, ":", C5) the answer will be: Time 1200 12 00 12:00 1820 18 20 18:20 1500 15 00 15:00 1730 17 30 17:30 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Need to pad with Zeros to the existing list of numbers in a column  New Users to Excel  
Is there a way to insert dashes in a column of numbers  New Users to Excel  
Insert colon in text in cell  Excel Discussion (Misc queries)  
How to add a 1 to existing column of phone numbers?  Excel Discussion (Misc queries)  
How do I insert hypens in existing column representing social sec.  Excel Discussion (Misc queries) 