Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Time: 11.45 = 11:45
Is there a way to be able to type it in a number (i.e. 11.45) and have it
display as a time (11:45)? When I format the cell to display as time I have to type the hour, a colon, and minutes. Is there any way around this? |
#2
|
|||
|
|||
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then With Target If IsNumeric(.Value) Then .Value = TimeSerial(Int(.Value), .Value * 100 Mod 100, 0) End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "lovelaughing" wrote in message ... Is there a way to be able to type it in a number (i.e. 11.45) and have it display as a time (11:45)? When I format the cell to display as time I have to type the hour, a colon, and minutes. Is there any way around this? |
#3
|
|||
|
|||
I am SO lost with that response! I am not sure what I just read! Are these
instruction on how to change the cells? I really appreciate you replying but could you bring it way down to a beginners instructions??!! :) "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then With Target If IsNumeric(.Value) Then .Value = TimeSerial(Int(.Value), .Value * 100 Mod 100, 0) End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "lovelaughing" wrote in message ... Is there a way to be able to type it in a number (i.e. 11.45) and have it display as a time (11:45)? When I format the cell to display as time I have to type the hour, a colon, and minutes. Is there any way around this? |
#4
|
|||
|
|||
The instructions are here
'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. copy the code from the answer you got and paste it, Bob's example is using the range H1:H10, you can change that to whatever you want the range to be http://www.mvps.org/dmcritchie/excel/install.htm you need a macro to get this conversion done or you can use autocorrect and replace a period with a colon -- Regards, Peo Sjoblom (No private emails please) "lovelaughing" wrote in message ... I am SO lost with that response! I am not sure what I just read! Are these instruction on how to change the cells? I really appreciate you replying but could you bring it way down to a beginners instructions??!! :) "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then With Target If IsNumeric(.Value) Then .Value = TimeSerial(Int(.Value), .Value * 100 Mod 100, 0) End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "lovelaughing" wrote in message ... Is there a way to be able to type it in a number (i.e. 11.45) and have it display as a time (11:45)? When I format the cell to display as time I have to type the hour, a colon, and minutes. Is there any way around this? |
#5
|
|||
|
|||
If you want a solution which doesn't involve macros, you can change the number
11.45 to a time of 11:45 with the formula: =TIME(INT(A1),MOD(A1,1)*100,0) To use this, you could set up your spreadsheet to enter the number in one cell, then have an adjacent cell use this formula to convert it to a time, then work with that. -- Regards, Fred Please reply to newsgroup, not e-mail "lovelaughing" wrote in message ... I am SO lost with that response! I am not sure what I just read! Are these instruction on how to change the cells? I really appreciate you replying but could you bring it way down to a beginners instructions??!! :) "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then With Target If IsNumeric(.Value) Then .Value = TimeSerial(Int(.Value), .Value * 100 Mod 100, 0) End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "lovelaughing" wrote in message ... Is there a way to be able to type it in a number (i.e. 11.45) and have it display as a time (11:45)? When I format the cell to display as time I have to type the hour, a colon, and minutes. Is there any way around this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time differences in a column | Excel Worksheet Functions | |||
Time Sheet Calculation Help Needed! | Excel Worksheet Functions | |||
Time Sheets | Excel Discussion (Misc queries) | |||
unmet challenge | Excel Worksheet Functions | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions |