Single Cell Formula

[Piper5299X]

I need to enter total minutes in a cell, but I want that cell to display in decimal hours. Example: I enter 360 in cell A5, but I want A5 to display 6.0
Another example: I enter 150 in cell A5, but I want A5 to display 2.5.
I've tried entering =Sum(A5/60) and =Sum(A5)/60 in cell A5.

I'm thinking VBA, but no idea how to do it. I've also tried the convert function, but I receive the #VALUE error.

 

[Ezguy4u]

What am I missing here. 360 minutes is 6 hours. 150 minutes is 2 point 5 hours. 85 minutes is 1 point 416667 hours. You get the answer by dividing minutes by 60. You don't need Sum. Formatting hours and minutes is always interesting. But in this case I think we just can do the math. Or give us an example of what wouldn't work.

 

[Piper5299X]

What am I missing here. 360 minutes is 6 hours. 150 minutes is 2 point 5 hours. 85 minutes is 1 point 416667 hours. You get the answer by dividing minutes by 60. You don't need Sum. Formatting hours and minutes is always interesting. But in this case I think we just can do the math. Or give us an example of what wouldn't work.

You're missing the part where I want the "6" or the "2.5" to show in the cell when the employee enters the total minutes they've worked. Not in a separate cell.

 

[yky]

I need to enter total minutes in a cell, but I want that cell to display in decimal hours. Example: I enter 360 in cell A5, but I want A5 to display 6.0
Another example: I enter 150 in cell A5, but I want A5 to display 2.5.
I've tried entering =Sum(A5/60) and =Sum(A5)/60 in cell A5.

As soon as you enter something in A5, the formula you have entered is gone. You may consider using a helper column, e.g. entering the formula in B5.

 

[Ezguy4u]

I get it now. This program will replace A5 with the decimal equivalent of minutes with hours. At least it is a start. We can carry on a discussion if you have more requirements.

Sub A5Time()

Dim Amt1 As Long

Amt1 = Range("A5")

Range("A5") = Amt1 / 60

End Sub

 

[Dave Patton]

You could use Paste Special Divide or the VBA

 

[Peter_SSs]

You could use a Worksheet_Change event code. The example below assumes that you want this only to apply in column A. To implement ..
1. Format column A as Number with 1 decimal place
2. Right click the sheet name tab and choose "View Code".
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window & test by entering/deleting/amending/pasting values in column A
5. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Columns("A"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If IsNumeric(c.Value) And Len(c.Value) > 0 Then c.Value = c.Value / 60
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[Piper5299X]

Here is an example of what I am doing. All of the columns/rows B5 through H32 the employee will be entering their total minutes they spent working on each commodity code (Column A) throughout the week.
I would like for this to display in decimal hours without the employee having to do the math. B5=.25, B6=.5, B7=.25 etc. etc. etc.

 

[Ezguy4u]

Peter SS I like your solution better. I even saved it to my library.

 

[Peter_SSs]

Peter SS I like your solution ..

Cheers. Glad to help.

All of the columns/rows B5 through H32 the employee will be entering their total minutes ..

If that is a fixed range then just make this change (if you haven't already )

Set Changed = Intersect(Target, Columns("A"))
Set Changed = Intersect(Target, Range("B5:H32"))

.. and format the range to 2 decimal places of course.