# Adding A Colon ( : ) To Convert A Standard Number Into A Time Format

May 6, 2009

Is there a way to enter a colon into a standard number to create a value that can be formatted into a 24 hour time value? eg a time is listed as 1345 with a general number format, and I want it returned as 13:45 witha custom format of hh:mm. Other than creating a table and using a vlookup function

## Time Format Why I Enter Colon Between The Hours And Minutes

Oct 28, 2007

I have used the format [hh]:mm in a cell for 24 hr clock calculations. Why do I have to enter the numbers with a colon when I populate the cells? Is there a way to set it up so I just type in the four numbers and the colon between the hours and minutes populates itself?

## Format Cells So 24 Hour Time May Be Input Without Colon

Nov 30, 2003

Is there a way to format cells so 24 hour time may be input without the colon, as an example 1425 instead or 14:25? We can do it in Access. One would think it possible in Excel, yet I have not discover how if indeed its possible.

## Convert Number Format To Time Format

Nov 28, 2013

How do I convert 11.20.00 in A1 to 11:20:00 in B1?

I've tried =TEXT(A1,"hh:mm:ss") to no avail.

## Convert Number To Date Time Format?

Dec 9, 2013

how to convert number to data time format:

eg. I have number 20121231230000 and I need its conversion to 31/12/2012 23:00.

I have tried to use fucntion date with left, mid, right but still I do not know how to extract time.

## Automatically Convert 4 Digit Number To Time Format

Aug 30, 2006

Is there any way that I can have a column of cells change the entered 4 digit to a time format? E.g. when somebody enters 1212 in a cell it will automatically convert the entered numbers to 12:12

## Convert Standard Numbers To Time

Jun 1, 2007

Is it possible to convert the following data to an Excel-recognized time format (easily, as I have numerous million-row files with time data like this). Some actual data is below with what it should be in parentheses.

0 (00:00)
1 (00:01)
13 (00:13)
57 (00:57)
145 (01:45)
308 (03:08)
900 (09:00)
1123 (11:23)
1334 (13:34)
2332 (23:32)

## Convert Standard Time In Military Time

Jun 21, 2007

I need a simple formula to convert standard time (1:05pm) into military time (13:08).

I've read through several posts, but all I've found is reformatting or time subtraction type information. The reformating works to an extent, it gives me the hours in military but the minutes stay standard.

Basicly, all I need is:

Cell A1 = 1:05 p
Cell B1 = formula that shows/converts 1:05 p as 13:08

## Convert Jd Edwards Julian Dates Format Into Standard Gregorian Date

Aug 9, 2007

how to convert the JD Edwards Julian date format into standard Gregorian date, and can it be done using VBA? Note that the JD Edwards Julian date is different than the standard Julian Date and uses 6 digits instead of 7.

## 0h 00m 00s Time Format To Standard?

Mar 23, 2014

I'm getting a time data on the format "0h 00m 00s" but I can't convert it to a standard "00:00:00" format to properly handle it and I'm not so Excel savvy. I already tried looking for something on google but the formulas for similar issues "0h 0s" or "0d 0h 0h" are not working (I can't find a way to adjust them). For example: I'm have the following data on G3:

0h 37m 52s

and I'm using the following TIME/FIND formula:

=TIME(FIND("h",G3),FIND("m",G3),FIND("s",G3))

but the outcome is "2:06:10" instead of "0:37:52"

## Converting Date And Time From Odd Text To Standard Format?

May 10, 2014

I'm copying data from another source to excel, and for certain date entries they come as text in the following format "On 14 May at 8:00". Is there anyway to convert multiple entries like this into a standard date format? With or without time is fine - just the date will do.

Dec 30, 2009

I am a basic excel user and what I want is to be able to add in a time and have the colon automatically entered for me. For example, I want to add 130 and have it come out to 1:30, or 1215 and have it come out to 12:15. Seems easy to me but I cannot figure it out.

## Time Format / How To Convert 10:9:8.13 To 10:09:08.013

Nov 29, 2013

I'm trying to sort a dataset that contains time stamps: each line is an event with a timestamp containing milliseconds and no leading zeroes:

if I sort, I get this result:

10:0:18:507
10:0:18:640
10:0:2:414
10:0:20:271
10:0:21:651
10:0:21:693
10:0:22:131
10:0:22:70

which is wrong on so many levels...

how can I correct this?

I wanted to convert to milliseconds but how to extract text relative to the delimiter ":"

I tried custom format, but Excel won't accept the formats I proposed "hh:mm:ss:000", "h:m:s:000", "00:00:00:000" it's all for the cat.

## Format Cell To Show Colon

Jan 12, 2006

Is there any way that I can format a cell to insert a colon three spaces to
the left from the end of any group of numbers that I type in the a cell?
Examples 9:15 10:15

## Convert Number Format To Text Format?

Oct 11, 2012

I want to convert number format to text format, any formula ?

## Input Time Without Colon

Oct 25, 2007

I am making a template for our security kiosk log and I wanted to simplify it as much as possible. I found the "time mask entry" and imputed that into the vba code. It was working for awhile however stopped working a few days ago. I went back and copied and pasted it again and changed the range to what I needed and an error came up stating "unable to set the NumberFormat property to the range class."

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vVal
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub

With Target

vVal = Format(.Value, "0000")
If IsNumeric(vVal) And Len(vVal) = 4 Then
Application.EnableEvents = False
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm"
End If
End With

Application.EnableEvents = True

End Sub

## Excel 2010 :: Convert Text To Number And Format Number Without 2 Decimal Places?

Oct 23, 2011

I have a problem that when I try to convert text to number and format the number without 2 decimal places as seen on the link I have given below, Instead of 1607.947, I get 1607947. I have Excel 2010 loaded. The details are in below picture.

[URL]

## Convert Decimal To 00:00:00 Time Format

Apr 9, 2014

How to be able to convert decimal hours into time format.

Here are the examples:

12.2 = 12:02:00
98.41 = 98:41:00
300.42.1 = 300:42:10

and so on and so forth...

## Convert Text To Time Format

Mar 5, 2014

If I had the below in a single cell what formula would I need for it to read just the 30mins in another cell in a time format?

17:30:00 + 00:30

## Convert Time In Seconds To 00:00:00 Format

Oct 17, 2013

I have a list of times in seconds similar to this,

212.027562
14.2672752
4.7557584
7.1336376

In the column next to this I have a accumulated time that needs to be in the format 00:00:00. Is this possible?

## Convert Date Time Format

Jan 26, 2007

I have a coloumn of cells in the format Dec 12 2005 07:04 PM
I need to convert them to the format 12/12/2005 19:04

## Convert Hh:mm.* To 12 Hour Time Format

Jun 12, 2008

I have a ton of time in the above format (hh:mm.*) that needs to be converted to a 12 hour format (2:15 PM). I'm not wonderful with formulas, but if it's explained "simplistic" enough, I can probably get it. Please help - I've googled this to death every possible way. I can go in an manually change 19:37.5 to 7:37 pm - but it will take me FOREVER!

## Adding Time :: Recognize Format Hh:mm:ss

Dec 17, 2008

I am importing from another program into Excel. Anytime I have a time in a column with a 0 in the 2nd hour place it will not add that time in. Example (05:21:32)

A1 10:20:12
A2 05:12:15
A3 10:15:12

Total= 20:35:24

How can I get excel to recognize a time in this format with a zero (05:34:23)?

## Delete String Before Last Colon If No Text After Last Colon

Nov 29, 2012

I found the following formula on another site that deletes all the text before the last colon (it deletes the colon as well)

=TRIM(RIGHT(M2,LEN(M2)-SEARCH("@#",SUBSTITUTE(M2,":","@#",LEN(M2)-LEN(SUBSTITUTE(M2,":",""))))))

Is it possible to modify it so that it only triggers if there is nothing after the last colon?

As you can see in the example below I have some notes entered by users and I would like to delete the ones where they didn't actually enter a note after the last colon:

11/05/2012 13:19:53 NEW MIKE : cld poe male sd cu not in till 3pm
10/18/2012 08:03:55 NEW HOSSEN : called no answer
10/15/2012 11:15:41 NEW HOSSEN :
10/12/2012 08:00:26 NEW HOSSEN :
10/11/2012 12:17:14 NEW HOSSEN : called no answer
11/14/2012 13:42:07 ACT MIKE : cld poe spk to cust sd cm back at 330

I would like to be left with just this instead:

11/05/2012 13:19:53 NEW MIKE : cld poe male sd cu not in till 3pm
10/18/2012 08:03:55 NEW HOSSEN : called no answer
10/11/2012 12:17:14 NEW HOSSEN : called no answer
11/14/2012 13:42:07 ACT MIKE : cld poe spk to cust sd cm back at 330

Link to article I found this formula in : [URL] .....

## Quick Time Formatting - How To Get Colon In The Middle

Nov 8, 2012

ColB is a time of day in 4 digits ****

I need it to be **:**

How do I get the colon in the middle?

## Convert Number To Time & Decimal Time

Oct 8, 2007

I need to separate a list of times from a telecommunications bill. I have 170,000 line of data.

The Times are represented as below.

eg

46 46 Sec
59 59 Sec
100 1 Min
159 1 min 59 sec
200 2 mins
502 5 mins 2 sec
1256 12 mins 56 sec
3456 34 min 56 sec.

How do I separate them to a decimalized per min value.

## Convert Decimal Numbers (Minutes) To Time Format

Mar 8, 2013

How to convert decimal numbers which represent minutes into time format? Like this

Sheet3  GH1MinutesTime212.9729729700:13:00325.9459459500:26:004116.756756801:57:00

## Find Time Format Cells And Convert To Date

Jan 24, 2007

I am pasting a large amount of data from a sql server query into excel. There is multiple tables output in each query. Some of the data is date/time and excel is formatting all of these cells to time. The date/time cells to not neatly line up in any row or column, so I cannot just format any give column or row. I need a macro that will find all of the cells that are formatted as time and change them to a date format.

## Convert Serial Date Time To General Format

Jul 28, 2007

I use software that exports date/time stamps in the format of: MM/DD/YYYY hh:mm:ss.s
I like to keep the data in this general format so that we can keep it consistent and it can be useful to others using other software.
I recently had to do some date/time stamp manipulations where I added 61 days to a dataset because the data was erroneously set to a diferent calendar. (Still don't know how that happened but it isn't relevent to this issue) When I set up a formula to add 61 days, it appears that it automatically converted it to the excel serial date/time format before adding the 61 days. Now I have a column of data that is mostly general format but has this portion in serial format. How can I get the serial format changed back to general so that the entire column is in the same format?

## Adding Years, Days, Months To Date/time Format

Apr 26, 2007

I have some cells which must be in the format 15/06/2007 15:25

I then need to add either days, months or years onto it.

Say the above date/time is in cell A1, when I do =YEAR(A1)+5 it displays 2012 if I choose the general cell format, but when I select the same cell format (date time) it comes out as 04/07/1905 00:00