Formatting for time

[Dan]

I have number in E3 (300) in column P I have the formula
=TEXT((TIME(,,E3)),"mm:ss") which nicely converts it to 5:00 or 5 minutes 299
= 4:59 etc.

what I an trying to do is eliminate column P and use a target formula so
that when the number is typed in E it converts it programatically. I did this
for converting to upper case and was using it as a base for this but I'm not
sure of the syntax for the target.formula:

If Target.Column = 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If Target.Column = 5 Then
On Error Resume Next 'insure that Enable Events gets reenabled
Application.EnableEvents = False
'Target.Formula = UCase(Target.Formula)
Target.Formula = Text("mm:ss")
Application.EnableEvents = True
On Error GoTo 0
End If

If Target.Column = 2 Then
On Error Resume Next 'insure that Enable Events gets reenabled
Application.EnableEvents = False
'Target.Formula = UCase(Target.Formula)
'Target.Formula = Text((Time(, , E3)), "mm:ss")
Target.Formula = Text("mm:ss")
Application.EnableEvents = True
On Error GoTo 0
End If


End Sub

I may be way off base but any suggestions or revisions or anything would be
greatly appreciated. Cheers

 

[John C]

I would recommend taking a look at Chip Pearson's page.
http://www.cpearson.com/excel/DateTimeEntry.htm

And modify as needed