You are here:

Excel/Input mask for time in Excel

Advertisement


Question
I am working on a large data project involving times in the format of hh:mm:ss.  It is extremely time consuming to type in the ":" for give or take 1 million records.  

I could not build a custom number format in Excel that adds the ":" and still treats the value as a time (since I have to do calculations with the time stamps)

I've looked online and it appears there is maybe a VB solution that might help.  I tried some of the cut and paste versions but I only got it working with h:mm and I don't have VB background to figure out how to make it work with hh:mm::ss.

Below is what I've tried:
****************

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


Thank You

Answer
Hi Anna,

Is the time imported as 6 digits; i.e. '095421'?  If so, we can insert a cell and use a formula instead of writing a macro in VB to accomplish this.

If the time is imported as above, and using the same example of '095421', you want this to be displayed as '09:54:21', follow the below steps:

Insert a New Column beside your time field. (Right-Click the column heading, Insert )
In the first blank field you will type in the formula - =TIME(LEFT(cell_reference,2),MID(cell_reference,3,2),RIGHT(cell_reference,2))

You will need to change the 'cell_reference' to match your sheet.  If your first date starts in cell F2, you would change the formula to =TIME(LEFT(F2,2),MID(F2,3,2),RIGHT(F2,2)) and hit Enter.

Once the first formula is in place, Right-click the cell and choose Format Cells... .  Under the Number tab, select the Custom Category and under Type, enter in hh:mm:ss .  Click OK .  Your cell should now display the correct time format you are looking for.

Now that this cell is properly formatted and formulated we want to copy this down to all rows.  Select the Column Header (the entire column highlights) and use Ctrl+D .  This will copy down the formula and all formatting that you have applied.

Select the original time column, Right-click and select Hide .

If you need any more help or your situation is a bit different, please let me know.

Kind Regards,
Steve
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Steve

Expertise

I am proficient in all areas of MS Excel, including Visual Basic (Macros), Charting, Graphing, Custom User Forms, Template Layout and Creation, Developer Add-ins, Active-X Controls, Setting Advanced Properties, XML Data and Workbook Protection.

Experience

I have an extensive educational, vocational and business background extending over 12 years in information technology.

Organizations
Association of Information Technology Professionals IEEE Computer Society Texas Professionals Texas Young Professionals Excel Developers Excel Blackbelts

Education/Credentials
Microsoft Certified Technology Specialist (MCTS) Program Microsoft Office Expert Program

©2016 About.com. All rights reserved.