A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How can I insert a colon into a column of existing numbers



 
 
Thread Tools Display Modes
  #1  
Old June 7th 07, 05:00 PM posted to microsoft.public.excel.misc
jcmonzon
external usenet poster
 
Posts: 1
Default 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  
Old June 7th 07, 05:41 PM posted to microsoft.public.excel.misc
Pete_UK
external usenet poster
 
Posts: 8,856
Default 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



  #3  
Old June 7th 07, 06:41 PM posted to microsoft.public.excel.misc
miketordoff
external usenet poster
 
Posts: 1
Default 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  
Old June 7th 07, 08:09 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
Default 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  
Old June 7th 07, 08:17 PM posted to microsoft.public.excel.misc
Ron Rosenfeld
external usenet poster
 
Posts: 5,651
Default 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  
Old June 7th 07, 08:24 PM posted to microsoft.public.excel.misc
ruchie
external usenet poster
 
Posts: 32
Default 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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 02:12 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.