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 |
Answer: How can I insert a colon into a column of existing numbers
To insert a colon into a column of existing numbers, follow these steps:
That's it! Now you should have a column of numbers with colons inserted between the hours and minutes. |
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 3-digit times (eg 930 to represent 9:30) and even 2-digit 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 one-digit 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 |
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 |
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 |
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 |
How can I insert a colon into a column of existing numbers
well theres a tedious 'quick-fix' 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 |
All times are GMT +1. The time now is 04:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com