Entering Time as HHMM; having it converted to HH:MM

The following problem seems to be solved in this thread: http://www.excelforum.com/excel-gene...11#post3346011 however, I don't seem to be able to get the solution to work.

"I need to enter elapsed start and stop times (military time, as hours and minutes) into a spreadsheet, for calculations of elapsed time in minutes (calculation used: stop time - start time * 24 * 60).

I hate having to reach over to enter the colon between the hours and minutes, as it really slows me down (there are 50-60 entries on each spreadsheet I need to do). Does anyone know a way that I can enter the start and stop times without any colons, commas or periods, and still have a calculation performed to provide me with elapsed minutes?"

Can anyone tell me where you would enter either of these codes? I am trying to achieve the same thing as TC was.

I have tried entering the code given in Neik Otten's post directly into the VBA in the "ThisWorkbook" object of the spreadsheet I am working on, and this doesn't appear to have done anything.

I have also tried entering Dav's code in the same place, and these also doesn't appear to have changed anything.

If anyone could help, I would really appreciate it.

Apologies if I what I am asking is something very basic!

Chris

if your time is in HHMM format then you can convert it through below formula

=TIME(0,RIGHT(A1,2),0)+TIME(LEFT(A1,IF(LEN(A1)=3,1,2)),0,0)

time in HHMM format in cell A1, then formula to be put in B1 just try & let me know

Thanks Amy.

What I would like, and it is something I have seen done on another spread sheet before, is to be able to enter HHMM into a cell, and for that same cell to be formatted as time. I think someone has solved this issue in the link I gave in my original post, however I am unable to get it to work! Are you able to help with this?

let me chek

Thanks again Amy!!

The person that wants to be able to do this was quite specific in that they want to be able to enter the figure as HHMM in one box, and for that same box to register the entry as time in the format HH:MM

Hi,

As per your post what I understood is, you have start & end time in HHMM format you need Total hours in HH:MM format by doing (End time ) – (Start time)

Then Put start time in cell A1 & end time in Cell B1 In HHMM format , & put below formula in cell C1 to get difference in HH:MM format

=IF(B1="","",TIME(0,RIGHT(B1,2),0)+TIME(LEFT(B1,IF(LEN(B1)=3,1,2)),0,0)-(TIME(0,RIGHT(A1,2),0)+TIME(LEFT(A1,IF(LEN(A1)=3,1,2)),0,0)))

Note: if your End time is Blank, then it will show Nothing in formula


i hope this is what you want ....

That would work, however it is a spreadsheet that we are looking to send to a client, and my boss is adamant that he wants each entry to register as a time!!!

i am afraid that it is possible...... lets wait for experts to revert on this

I think the problem is solved by using VBA in the link I originally gave, I just don't know how to implement the solution!!

Is anybody able to help with this? I can send a copy of a spreadsheet that does what I am trying to do, however, I am unable to replicate what it is doing in other spreadsheets. Even copying and pasting the the working spreadsheet, and then adapting it, doesn't work. The working spreadsheet only does what I want it to do in one column.

Hi Marcol,

No, in the spreadsheet I have, you enter the time as a 4 digit text string, and then the same cell that you entered the time in displays the time as HH:MM.

See attached. (Using Active X?)

Time.xlsm

So what isn't working as required on your sheet?

try this it should work

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TimeStrText As String
Dim UserInput
On Error GoTo EndMacro

    If Application.Intersect(Target, Range("E1:E99, F1:F99")) Is Nothing Then
       If Not Selection.Cells.Count = 1 Then
       ' Application.Undo
            Application.EnableEvents = False
            If Target.Value < 1 Then
                UserInput = Format(Int(Target.Value * 24) * 100 + ((Target.Value * 24) - Int(Target.Value * 24)) * 60, "000")
            Else
                UserInput = Format(Target.Value, "000")
            End If
                If Len(UserInput) > 1 Then
                    Target = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
                End If
                Application.EnableEvents = True
                Exit Sub
        
    End If
    Exit Sub
End If
    If Target.Cells.Count > 1 Then
        Exit Sub
    End If
    If Target.Value = "" Then
        Exit Sub
    End If
    If Target.Column = "1" Then
        Exit Sub
    End If
    Application.EnableEvents = False
    With Target
        If .HasFormula = False Then
                Select Case Len(.Value)
                    Case 1 ' e.g., 1 = 00:01
                        TimeStrText = "00:0" & .Value
                    Case 2 ' e.g., 12 = 00:12 AM
                        TimeStrText = "00:" & .Value
                    Case 3 ' e.g., 735 = 7:35 AM
                        TimeStrText = Left(.Value, 1) & ":" & _
                        Right(.Value, 2)
                    Case 4 ' e.g., 1234 = 12:34
                        TimeStrText = Left(.Value, 2) & ":" & _
                        Right(.Value, 2)
                    Case Else
                        Err.Raise 0
                End Select
                    .Value = TimeValue(TimeStrText)
        End If
    End With
Application.EnableEvents = True
Exit Sub
EndMacro:

Application.EnableEvents = True
End Sub

Marcol, I can't replicate column E in any other columns on the sheet, or on a different spread sheet.

Toonies, thanks. How do I enter that?

Format the columns HH:MM and follow Macrol advise

Thanks. That works great.

For anyone trying to replicate this that is as ignorant as I am:

(Excel 2010) To enter the code you need to enter the visual basic editor on Excel. This can be done by showing the "Developer" tab on the ribbon, and on the "Developer" tab, selecting Visual Basic.

In the visual basic editor, in the "project" window that should appear on the left hand side, open the VBAproject/spreadsheet you are working on, open the "Microsoft Excel Objects" folder, and double click "This Workbook".

In the new window that appears, in the first drop down box, select "Workbook", and in the second drop down box select the appropriate option, as per Marcol's last post.

In the between the two lines that appeared in the main box, when the option in the second drop down box was selected, copy and paste Toonies code, excluding "Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)" at the top of Toonie's code, and "End Sub" at the bottom of his code.

The code will now be working for "E1:E99" and "F1:F99". To get it to work for other cells, edit references to "E1:E99" and "F1:F99" in the code itself, so that the cells you want the code to work for are referenced.

Save the visual basic changes.