convert string to specific time format

[rossbritton26]

hi,

i would like to be able to input a time as ss00 (without any colons, commas or fullstops) and then require the time entered to be formatted and displayed as ss.00.

also, occassionally the times may be just over 1 min so would be something like 1:01.34, i would like to be able to put this in as mss00 and then be formatted and displayed as m:ss.00.

any help would be much appreciated.

 

[cova]

This formula would convert it (doesnt curently work for if you just write s00, has to be ss00, but that can be tweaked)
If your entry was in E4:

=IFERROR(IFERROR(MID(E4,LEN(E4)-LEN(RIGHT(E4,5)),2),MID(E4,LEN(E4)-LEN(RIGHT(E4,4)),1))&":","")&MID(E4,LEN(E4)-LEN(RIGHT(E4,3)),2)&"."&RIGHT(E4,2)

 

[cova]

Have improved on that a bit more. Converted it to VBA so that it will now replace the value you enter with the correct format. Just copy and paste this into your sheets VBA

Private Sub Worksheet_Change(ByVal Target As Range)Dim numinsert As String, testcell As Range


    Set testcell = Range("E2:E50") 'change this range to the cell range where they will be entering the values you want changing


    If Not Intersect(Target, testcell) Is Nothing Then
   
        If Target.Value Like FormulaR1C1 Or Target.Value Like "*:*" Or Target.Value Like "*.*" Then
        Else
        numinsert = Target.Value
        Debug.Print "=IFERROR(IFERROR(IFERROR(MID(" & numinsert & ",LEN(" & numinsert & ")-LEN(RIGHT(" & numinsert & ",5)),2),MID(" & numinsert & ",LEN(" & numinsert & ")-LEN(RIGHT(" & numinsert & ",4)),1))&"":"","""")&MID(" & numinsert & ",LEN(" & numinsert & ")-LEN(RIGHT(" & numinsert & ",3)),2)&"".""&RIGHT(" & numinsert & ",2),LEFT(" & numinsert & ",1)&"".""&RIGHT(" & numinsert & ",2))"
        Target.FormulaR1C1 = "=IFERROR(IFERROR(IFERROR(MID(" & numinsert & ",LEN(" & numinsert & ")-LEN(RIGHT(" & numinsert & ",5)),2),MID(" & numinsert & ",LEN(" & numinsert & ")-LEN(RIGHT(" & numinsert & ",4)),1))&"":"","""")&MID(" & numinsert & ",LEN(" & numinsert & ")-LEN(RIGHT(" & numinsert & ",3)),2)&"".""&RIGHT(" & numinsert & ",2),LEFT(" & numinsert & ",1)&"".""&RIGHT(" & numinsert & ",2))"
        End If


    End If
End Sub

I also fixed the issue with having s00, so that works now. All you need to do is change the cell range in the code

 

[rossbritton26]

thank you very much cova both options worked brilliantly thank you