Excel/Input mask for time in Excel
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)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub
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"
Application.EnableEvents = True
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.