0

Enter time date in excel in mm:ss format(just buy inputting numbers without the colon : and 0: hours in front)

Hi, I'm working on a project and need to calculate the number of minutes and seconds people worked on.

*I found a similar question but was wondering if I could make the format more simple. (Will explain at the bottom)

Not the issue is when input 38:14 meaning 38 minutes and 14 seconds the cell recognizes it as 38 hours and 14 minutes and if i double click it... this is what I get... 1900-01-01 2:14:00

I understand the reason why but I want a format that could recognize the numbers as minutes and seconds.

I found an answer that seems easy and simple but i can't figure out how to make it work...

"Peter Albert gave a solution in this link. I like his answer simple just need to follow simple directions and conpy and paste but it's not working for me"

here's his answer.

There is a solution! Place this VBA code in your worksheet module, i.e.:

Open the Visual Basic Editor (Alt-F11) In the top left treeview, double click on the sheet you want to enter your times In the central code panel, place the below code. Close VBE Use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value < 0 Or Target.Value > 1 And Target.NumberFormat <> "h:mm" Then Exit Sub
    Application.EnableEvents = False
    Target.Value = Target.Value / 60
    Target.NumberFormat = "mm:ss"
    Application.EnableEvents = True
End Sub
In case you already formatted the range you're entering the data in, use this line instead as the first line:

If Target.Value < 0 Or Target.Value > 1 Then Exit Sub
Note that this will change the value and format - every time you enter either a time - or something that is between 0 and 1! If you want to restrict it to a certain column, add this line:

If Target.Column <> 3 Then Exit Sub
or this line to restrict it to a certain range

If Intersect(Target, Range("A2:A100") Is Nothing Then Exit Sub

===================================================

*Now the version I like to have is when I input 4 digits I want the 2 first ones to be recognized as minutes and the two last ones as seconds... This would help me save time typing the : colon.

so when I input 3814 it would be recognized as 38 minute and 14 seconds in the cell it would be marked as 38:14 automatically.

of course, even if it passes 60 minutes I would want it to display minutes only.

The cells I want to apply the conditions are from G13 to G104.

Thank you in advance.

Run5k
  • 16,044
  • 24
  • 51
  • 65
Richard J. Park
  • 1

1 Answers1

0

First clear the data entry cells and format them to Text.

Then install this event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Intrsct As Range, Cell As Range, s As String
    Dim mins As Long, secs As Long, t As Date

    Set Intrsct = Intersect(Range("G13:G104"), Target)
    If Intrsct Is Nothing Then Exit Sub

    Application.EnableEvents = False
        For Each Cell In Intrsct
            s = Cell.Text
            If s = "" Then
                Cell.NumberFormat = "@"
            ElseIf Len(s) > 4 Then
                Cell.Clear
                Cell.NumberFormat = "@"
             Else
                s = Right("0000" & s, 4)
                If s Like "####" Then
                    mins = CLng(Left(s, 2))
                    secs = CLng(Right(s, 2))
                    t = TimeSerial(0, mins, secs)
                    Cell.NumberFormat = "[mm]:ss"
                    Cell.Value = t
                Else
                    Cell.Clear
                    Cell.NumberFormat = "@"
                End If
            End If
        Next Cell
    Application.EnableEvents = True
End Sub

If you enter 1234 you will get 12:34
If you enter 1 you will get 00:01
If you enter:12 you will get 00:12
If you enter 123 you will get 01:23
If you enter 9999 you will get 100:93
etc.

Remember:

The macro will only work if the entry cell is cleared and formatted as Text before you enter data in it.

Gary's Student
  • 19,396