Input 4 digits into a cell, convert to [mm]:ss

[Jockster]

Entering time values complete with colons is time consuming.
I am hoping to find a solution to this by being able to enter a four digit number (i.e. 6609) which will be converted to 66:09 in [mm]:ss format.
The times then can be used in calculations.

I have the following code which works well when converting four digits to [hh]:mm format, but I need to adapt it to [mm]:ss and can't figure it out...

With rCell
    
    Dim Hrs As Integer
    Dim Mins As Integer
    
    '''convert the numeric value to a time value
       Hrs= rCell.Value \ 100
        Mins = rCell.Value Mod 100
        rCell.Value = (Hrs + Mins / 60 / 24)
        rCell.NumberFormat = "[hh]:mm"

End With

Any pointers appreciated!

 

[Jockster]

 rCell.Value = (Hrs + Mins / 60) / 24

In my example, this close bracket is in the wrong place.
Doesn't solve my issue though!

 

[FormR]

Hi, here is one option you could try:

With rCell
    .Value = Evaluate("0+TEXT(" & .Address & ",""00\:00\:00"")")
    .NumberFormat = "[mm]:ss"
End With

 

[Jockster]

Spot on FormR - works a treat.
Many thanks.

 

[ask2tsp]

Right-click the sheet's label, view code and paste the following code

Option Explicit
Const mmssCell As String = "B1" '<=== change to your cell address

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = mmssCell Then dec2mmss
End Sub

Sub dec2mmss()
    Dim celToConvert As Range
    Dim minutes As Integer
    Dim seconds As Integer
    
    'do not trigger a worksheet_change event _
     by change applied in this sub

    Application.EnableEvents = False
    
    Set celToConvert = Me.Range(mmssCell)
    
    minutes = celToConvert.Value \ 100
    seconds = celToConvert.Value Mod 100
    celToConvert.Value = TimeSerial(0, minutes, seconds)
    celToConvert.NumberFormat = "mm:ss"
    
    Application.EnableEvents = True
End Sub