Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting for time
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting for time
I would recommend taking a look at Chip Pearson's page.
http://www.cpearson.com/excel/DateTimeEntry.htm And modify as needed -- ** John C ** "Dan" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting time | Excel Discussion (Misc queries) | |||
Time formatting hh:mm:ss | Excel Discussion (Misc queries) | |||
time formatting | Excel Discussion (Misc queries) | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions |