How can multiply a value I enter in an excel cell by a constant number, with result on appearing on same cell?

Question

I have an Excel spreadsheet where I need to enter values in days in cells in one column, and automagically convert those to seconds. How can I do this?

For example, say I want to have values in column B reflected as seconds. So in B1 I type 1, press Enter, and now the value in B1 is 86400; in B2 I type 2, press Enter, B2 becomes 172800.

Clearer?

Answer 1

If you want to automagically convert any number typed into column D to seconds as a true time value then right-click the worksheet's name tab and choose View Code. When the VBE opens, paste the following into the pane on the right side titled something like Book1 - Sheet1 (Code).

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Columns("A")) Is Nothing Then
        On Error GoTo Erreur
        Application.EnableEvents = False
        Dim a As Range, ms As Boolean
        For Each a In Intersect(Target, Columns("A"))
            If IsNumeric(a.Value) Then
                If a.Value <> 0 Then
                    a.Offset(0, 1) = a.Value * 86400
                Else
                    a.Offset(0, 1).ClearContents
                End If
            End If
        Next a
    End If
    GoTo Fìn
Erreur:
    Debug.Print Err
Fìn:
    Application.EnableEvents = True
End Sub

Tap Alt+Q to return to your worksheet and start seconds as numbers into column D.

I'm walking the integer or double typed into the cell back as first hours then minutes and finally seconds. I find this the best way to retain the accuranacy of any milliseconds in the value given Excel's floating point precision significance of 15 digits.

EDIT: Modified to more closely approximate the OP's edited question

Answer 2

I modified Jeeped code to meet my needs

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Columns("D")) Is Nothing Then
        On Error GoTo Erreur
        Application.EnableEvents = False
        Dim d As Range
        For Each d In Intersect(Target, Columns("D"))
            If IsNumeric(d.Value) And d.Value > 0 Then
                ' Interested in a 8-hour day
                d.Value = d.Value * 3600 * 8
            Else
                d.Value = ""
            End If
        Next d
    End If
    GoTo Fìn
Erreur:
    Debug.Print Err
Fìn:
    Application.EnableEvents = True
End Sub

Answer 3

Without VBA you can use a formula column, similar to the following example:

That is, you complete the Days column, and entries in the Seconds column will take the values in the Days column and multiply them by 86000 (24 hours per day x 60 minutes per hour x 60 seconds per minute). This is probably the best way to do this, as you still maintain the original data set (Days).

With VBA you can tap into the Change procedure associated with the Worksheet object using something like:

Dim redo As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ChangedCell As Variant

If Target.Count = 1 Then
  For Each ChangedCell In Target
    If redo Then
      If (ChangedCell.Column = 2) And (ChangedCell.Row > 1) Then
        redo = False
        ChangedCell.Value = ChangedCell.Value * 86400 ' * 24 * 60 * 60
      End If
    Else
      redo = True
    End If
  Next
End If
End Sub

The above code cycles through each element within the Target range (all the cells that changed) and updates them using the same formula as mentioned previously (multiplying the call .Value with 86400) only if the cell is within a specific range. The specific range mentioned above are cells in Column 2 and Row > 1. It's to avoid any calculation outside this range. Also, since the Change procedure is executed with each change, you may run into recursively updating your sheet. For that, a Boolean redo is created to avoid recalculating any changes.

While using a VBA solution is great, you lose the original data set with this update, so I would advise against it.

Finally, you could use a Paste-Special method, where you copy the value 86400 (from anywhere on your sheet), then select the range you're interested in updating and Paste Special > Operation > Multiply: