Is it possible to make a script or function in Excel to transform entered time data?

[Mogster]

Hello everyone,

I am dealing with a large group of users who are used to entering time data as mm:ss or h:mm:ss, but seem to be struggling to enter a zero for the hour when the value is under 1 hour. Is it possible to make a script transform the entered data based on its length to the proper values?

ie. if the length of the data entered in the cell is 4 or 5 characters long, examples: 1:23, 13:50 it would transform these entered values to be stored as mm:ss not hh:mm?

If the data length is 7 or 8, it would treat it properly as h:mm:ss. (1:44:50)

If anyone has any suggestions or could guide me in the right direction, it's much appreciated. Thanks!

 

[ZMyrrh]

You will need to change some of this but here is what I have so far. If you could send either a picture or excel file of that would help a lot on understanding where the information is coming from and where you want the code to throw the new data. I'm going to paste what I have so far just in case you are familiar with VBA.

Public Sub correction()
Dim word As String: word = ActiveCell.Text
Dim length As Integer: length = Len(word)

If length = 4 Then

ActiveCell.Offset(0, 1).Value = Format("0:" & Left(word, 1) & ":" & Mid(word, 3, 2), "hh:mm:ss")

ElseIf length = 5 Then

ActiveCell.Offset(0, 1).Value = Format("0:" & Left(word, 2) & ":" & Mid(word, 4, 2), "hh:mm:ss")

Else

ActiveCell.Offset(0, 1).Value = Format(word, "hh:mm:ss")


End If

End Sub