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

 

[AlanY]

it works for me

Book1
	W	X	Y	Z
4
5		mins	hrs:mins
6		120	2:00
7
Sheet5

Cell Formulas
Range	Formula
Y6	Y6	=X6/24/60

 

[CatRoberts]

it works for me

Book1
	W	X	Y	Z
4
5		mins	hrs:mins
6		120	2:00
7
Sheet5

Cell Formulas
Range	Formula
Y6	Y6	=X6/24/60

Hi Alan,

Thanks for this. It works if I put the formula into another cell, then I type my minutes into X6 (120). It then puts the answer (2:00) into the 'other' cell where the formula is.

However, I was hoping for the hours and minutes answer to be in X6 - so when I type 120 into X6 it changes to 2:00 automatically. Does that make sense? It is even possible?

Many thanks

Cat

 

[MARK858]

Every time I enter the formula into the cell X6....
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

You can't put the formula in the same cell as the data or reference the same cell in the formula which is why you get the circular reference error. You would need VBA to achieve that result.

 

[pjmorris]

The key thing is that you want to type one thing and then have it interpreted as something else (Excel like most computery things is pretty dumb). If its important to enter a duration in minutes in X6 then, if the figure is to be used elsewhere in the spreadsheet, simply change the references to X6 to (X6/1440). Alternatively, why not simply type 2:00 into X6.

Not sure this helps.

 

[MARK858]

Just in case you decide that you do want VBA for this put the code below in the Worksheet module (right click your sheet tab, click view code and paste in the window that appears).

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

 

[CatRoberts]

The key thing is that you want to type one thing and then have it interpreted as something else (Excel like most computery things is pretty dumb). If its important to enter a duration in minutes in X6 then, if the figure is to be used elsewhere in the spreadsheet, simply change the references to X6 to (X6/1440). Alternatively, why not simply type 2:00 into X6.

Not sure this helps.

Thanks Peter,

I have been doing just that and working it out myself and typing 2:00 etc... I was hoping for a easy way to do it to save me time (and my brain power!) but it seems it's not possible without a VBA, far too complex for me ;o)

I'll keep persevering...

Many thanks

Cat

 

[CatRoberts]

Just in case you decide that you do want VBA for this put the code below in the Worksheet module (right click your sheet tab, click view code and paste in the window that appears).

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

Thanks Mark,

I'll give it a try

 

[MARK858]

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

 

[pjmorris]

Mark, what was wrong with your original code? It does have the Application.EnableEvents=True statement in post 6? am I missing something?