Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 7th 07, 05:00 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
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

  #2   Report Post  
Old June 7th 07, 05:41 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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   Report Post  
Old June 7th 07, 06:41 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
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   Report Post  
Old June 7th 07, 08:09 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
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   Report Post  
Old June 7th 07, 08:17 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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   Report Post  
Old June 7th 07, 08:24 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: May 2007
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 09:16 AM
How to add a 1 to existing column of phone numbers? Jan Excel Discussion (Misc queries) 3 February 15th 05 04:38 PM
How do I insert hypens in existing column representing social sec. hard head Excel Discussion (Misc queries) 1 January 5th 05 06:47 PM


All times are GMT +1. The time now is 03:40 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017