Formula for changing minutes to hours and minutes

[CatRoberts]

Hi all,

I'm trying to add a formula to my workbook to convert minutes to hours and minutes, so 120 to read 2:00 for example.

I've used a couple of different formulas: =X6/1440 and format [h]:mm and also =X6/(24*60) then format the cell as [hh]:mm

Every time I enter the formula into the cell X6, firstly I get a circular reference error, then it changes the formula to read: 29/04/1900 00:00:00 so my return is - 2880:02

I tried putting the formula into a different cell to remove the circular reference error but it's still changing the formula to 29/04/1900 00:00:00 when I press save.

I have no idea what I'm doing wrong?

Any advice?

Many thanks

Cat

 

[MARK858]

The Application.EnableEvents = False was above the IF statement that exits the sub so if the statement was true then it would exit the sub with Events set as False.

 

[pjmorris]

many thanks - a good catch and avoids hours of frustration!

 

[CatRoberts]

Sorry, major issue with what I posted,
Run the code below if you have run the previous code (it only needs running this once)

Sub resetEvt()
   Application.EnableEvents = True
End Sub

then you can use the code below

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    Application.EnableEvents = False
    If Target.Address = "$X$6" Then
        Target.Value = Target.Value / 60 / 24
        Target.NumberFormat = "[hh]:mm"
    End If
    Application.EnableEvents = True
End Sub

Mark! You're a genius! It works and I even managed to add and run it.

Is it difficult now to apply it to other cells in the sheet? I'd like it to work for:

X6:X16
Y6:Y16
Z6:Z16
AA6:AA16
AB6:AB16
AC6:AC16

Would I re-create this code for each individual cell and add it in? or is there a simpler way?

Thanks again

Cat

 

[MARK858]

Try...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    Application.EnableEvents = False
    If Not Intersect(Target, Range("X6:X16,Y6:Y16,Z6:Z16,AA6:AA16,AB6:AB16,AC6: AC16")) Is Nothing Then
        Target.Value = Target.Value / 60 / 24
        Target.NumberFormat = "[hh]:mm"
    End If
    Application.EnableEvents = True
End Sub

 

[CatRoberts]

Try...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    Application.EnableEvents = False
    If Not Intersect(Target, Range("X6:X16,Y6:Y16,Z6:Z16,AA6:AA16,AB6:AB16,AC6: AC16")) Is Nothing Then
        Target.Value = Target.Value / 60 / 24
        Target.NumberFormat = "[hh]:mm"
    End If
    Application.EnableEvents = True
End Sub

Yaaaaaaay!!!! Thank you so much, It works!

 

[MARK858]

You're welcome (please note that shouldn't be a space between AC6: and AC16).