How To Enter Time In Excel Without Using Colon

Please Annotation: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If yous are using an before version (Excel 2003 or earlier), this tip may non work for yous. For a version of this tip written specifically for earlier versions of Excel, click hither: Entering or Importing Times without Colons.

Entering or Importing Times without Colons

When you enter a time into a cell, Excel keys on the presence of the colon between the hour and minute portions of the time. Because of the position of the colon on the keyboard, however, entering a colon for each fourth dimension value that y'all enter can dull you down—particularly if you accept quite a few time values to enter.

For this reason, you may wonder if at that place is a way to skip entering the colon and either have them entered automatically or entered all at once. Entering them automatically takes a bit more doing, requiring the use of a macro, and will be covered shortly. Entering the colons all at in one case can be washed with a formula, as in the following:

=TIMEVALUE(REPLACE(A1,iii,0,":"))

This formula assumes that the time value (without a colon) is in cell A1, and that it is comprised of iv digits. Thus, if cell A1 contains a value such as 1422, then the formula returns 14:22 as an actual fourth dimension value. (You may demand to format the cell as a time value.)

If your original entry cell might contain a time that uses just 3 digits, such equally 813 instead of 0813, then y'all need to utilise a slightly different formula:

=Fourth dimension(LEFT(A1,LEN(A1)-2),RIGHT(A1,two),0)

If you prefer for the insertion of the colons to happen automatically, y'all tin employ a macro. You can create a macro that volition examine a range of cells where you programme on adding dates to the worksheet, and so insert the colon in the entry. This is washed by creating a macro that is triggered past the SheetChange issue. The following macro is one such:

Individual Sub Workbook_SheetChange(ByVal Sh As Object, _   ByVal Target Every bit Excel.Range)     Dim TimeStr As String      On Mistake GoTo EndMacro     If Application.Intersect(Target, Range("C7:D15")) Is Nil And then         Go out Sub     End If     If Target.Cells.Count > 1 Then         Get out Sub     End If     If Target.Value = "" Then         Exit Sub     End If      Application.EnableEvents = Imitation     With Target         If .HasFormula = False And then             Select Example Len(.Value)                 Case ane ' e.g., 1 = 00:01 AM                     TimeStr = "00:0" & .Value                 Case 2 ' due east.g., 12 = 00:12 AM                     TimeStr = "00:" & .Value                 Case three ' e.g., 735 = seven:35 AM                     TimeStr = Left(.Value, 1) & ":" & _                     Right(.Value, 2)                 Case iv ' e.1000., 1234 = 12:34                     TimeStr = Left(.Value, 2) & ":" & _                     Right(.Value, 2)                 Case Else                     Err.Raise 0             Finish Select             .Value = TimeValue(TimeStr)         Terminate If     End With     Application.EnableEvents = Truthful      Exit Sub      EndMacro:     MsgBox "You did not enter a valid time"     Application.EnableEvents = True     ActiveCell.Offset(-1, 0).Select Terminate Sub

The first thing the macro does is to check to see if the data that was just entered was in the range C7:D15. If it wasn't, and so the macro exits. It also checks to make sure that there is simply a single prison cell selected and that the cell isn't empty. If all these criteria are met,, then the macro checks the length of the value in the cell and pads it out with leading zeroes, every bit necessary. This macro is based on a macro found at Chip Pearson'southward site, hither:

http://cpearson.com/excel/DateTimeEntry.htm

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for toll-effective Microsoft Excel training. This tip (12205) applies to Microsoft Excel 2007 and 2010. You lot can find a version of this tip for the older carte du jour interface of Excel hither: Inbound or Importing Times without Colons.

Author Bio

With more l non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized writer. He is president of Sharon Parq Associates, a reckoner and publishing services visitor. Larn more about Allen...