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 seconds

thanks!

Thanks,
REMGU


See More: Convert milliseconds to seconds on the fly in Excel

Report •

#1
April 24, 2012 at 10:00:40
✔ Best Answer
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 •

Ask Question