# Solved Convert milliseconds to seconds on the fly in Excel April 24, 2012 at 07:18:37
Specs: Win7, 4gb
 In Excel 2010, how can I convert milliseconds to seconds (ss.00) on the fly as I type the data into the specific cells?i.e.123456 milliseconds = 123.55 secondsthanks!Thanks,REMGU See More: Convert milliseconds to seconds on the fly in Excel

#1 April 24, 2012 at 10:00:40
 Without a macro, just use put a conversion formula in another column.Otherwise, you'll need a Worksheet_Change macro that will monitor changes made to the cells and replace what is manually entered with the converted value.This will perform the conversion on any number entered in Column A:```Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Column = 1 Then If IsNumeric(Target) Then Target = Target / 1000 End If End If Application.EnableEvents = True End Sub```Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#2
April 26, 2012 at 07:30:40
 Thank you! Is there a way to overwrite a specific cell? So that I could type any value? I find that if I type a value and then delete it, the value in the cell is 0. This throws off some calcs.Thanks!Thanks,REMGU

Report •

#3 April 26, 2012 at 11:51:47
 I don't know what you mean by "overwrite a specific cell".How does a 0 "throw off off some calcs"?I need some more details/specifics before I can off anything else.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

#4
April 26, 2012 at 12:03:49
 I am averaging based on those cells and having zeros throws the averages off. But I fulled around some and found out how to do it. The question I have now is if this could be simplified, especially the first part that converts the milliseconds to seconds. The spreadsheet is a little slow when entering values.``` Rem This VBA code snippet will convert the millisecond input to seconds, on the fly Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Column = 5 Then If IsNumeric(Target) Then Target = Target / 1000 End If End If If Target.Column = 6 Then If IsNumeric(Target) Then Target = Target / 1000 End If End If If Target.Column = 7 Then If IsNumeric(Target) Then Target = Target / 1000 End If End If If Target.Column = 8 Then If IsNumeric(Target) Then Target = Target / 1000 End If End If If Target.Column = 9 Then If IsNumeric(Target) Then Target = Target / 1000 End If End If If Target.Column = 11 Then If IsNumeric(Target) Then Target = Target / 1000 End If End If If Target.Column = 12 Then If IsNumeric(Target) Then Target = Target / 1000 End If End If If Target.Column = 13 Then If IsNumeric(Target) Then Target = Target / 1000 End If End If If Target.Column = 14 Then If IsNumeric(Target) Then Target = Target / 1000 End If End If If Target.Column = 15 Then If IsNumeric(Target) Then Target = Target / 1000 End If End If Rem This VBA code snippet will clear any cell that has a value of zero, on the fly. Dim myRange1 As Range Set myRange1 = Range("E7:I250") For Each C In myRange1 If C.Value = 0 Then C.Value = "" End If Next Dim myRange2 As Range Set myRange2 = Range("K7:O250") For Each C In myRange2 If C.Value = 0 Then C.Value = "" End If Next Application.EnableEvents = True End Sub ```Thanks,REMGU

Report •

#5 April 26, 2012 at 18:51:58
 Your code does not convert the number 10. Was that done on purpose?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#6
April 27, 2012 at 08:42:53
 Yeah only when the value of the cell is truly zero. So '10' or '0.001' would not convert. Thanks.Thanks,REMGU

Report •

#7 April 27, 2012 at 10:15:27
 To simply the part of the code that divides the values by 1000, try this:```Private Sub Worksheet_Change(ByVal Target As Range) Select Case Target.Column Case 5 To 9, 11 To 15 If IsNumeric(Target) Then Application.EnableEvents = False Target = Target / 1000 Application.EnableEvents = True End If End Select End Sub```To simplify the part that is causing the "average" issue, I would do it with a spreadsheet formula, not by eliminating the values via VBA. Looping through your ranges, cell by cell, is going to take a long time.A simple solution to not include 0's in an average is to use this formula:=SUM(A1:A100) / (COUNT(A1:A100) - COUNTIF(A1:A100,"=0"))This seems to work for the situation where you are deleting a value as you mentioned in Response # 2.Other methods, to handle other situations, can be found here:http://www.ozgrid.com/Excel/average...Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report • 