If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#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 
Ads 
#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 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Need to pad with Zeros to the existing list of numbers in a column  dwaraka  New Users to Excel  1  May 22nd 07 10:53 PM 
Is there a way to insert dashes in a column of numbers  Mickie  New Users to Excel  6  May 27th 06 02:58 PM 
Insert colon in text in cell  Phoenix  Excel Discussion (Misc queries)  4  February 3rd 06 10:16 AM 
How to add a 1 to existing column of phone numbers?  Jan  Excel Discussion (Misc queries)  3  February 15th 05 05:38 PM 
How do I insert hypens in existing column representing social sec.  hard head  Excel Discussion (Misc queries)  1  January 5th 05 07:47 PM 