Converting minutes to Hours

[searchingforhelp]

Hi, I hope all is well.
This sheet's objective is to count how many minutes it takes me to complete a project. I created a basic formula, to sum up the numbers in column I. However, the formula in I18 gives me an incorrect amount of 4.25 instead of 21:40. Also, the formula has created unnecessary rows 19-25 for some reason. Is there a better formula to convert the minutes to hours that will also eliminate the unnecessary rows?

Thanking you in advance for any help you can provide.

Timesheet.xlsx
	A	B	C	D	E	F	G	H	I
6	Items	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Total Minutes Worked
7		7/24/2023	7/25/2023	7/26/2023	7/27/2023	7/28/2023	7/29/2023	7/30/2023
8	Project 1	30	60	45	10	120			265
9	Project 2
10	Project 3
11	Project 4
12	Project 5
13	Project 6
14	Project 7
15	Project 8
16	Project 9	300	400	300	200	100			1300
17
18								Grand Total H:M	4:25
19									0
20									0
21									0
22									0
23									0
24									0
25									0
26									21:40
7-24-2023

Cell Formulas
Range	Formula
I8:I16	I8	=SUM(B8:H8)
I18:I26	I18	=I8:I16/24/60
Dynamic array formulas.

 

[Fluff]

Why is 4:25 wrong & 21:40 correct?
If you want the total hours worked shouldn't it be 26:05?

 

[Anonymous1378]

to sum up the numbers in column I

1) The SUM() function is nowhere to be seen
2) Why are you expecting 21:40 as your total when 265+1300 is 1565, which is greater than the 1440 minutes in a day?

 

[searchingforhelp]

Why is 4:25 wrong & 21:40 correct?
If you want the total hours worked shouldn't it be 26:05?

Thank you Fluff, if the total hours worked should be 26.05, how can I have the total in one cell verse how it is broken down?

 

[Fluff]

Yup, try

=SUM(I8:I16)/1440

 

[searchingforhelp]

1) The SUM() function is nowhere to be seen
2) Why are you expecting 21:40 as your total when 265+1300 is 1565, which is greater than the 1440 minutes in a day?

Thank you Anonymous, yes the minutes in row 16 is an exaggeration. I adjusted

Timesheet.xlsx
	A	B	C	D	E	F	G	H	I
6	Items	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Total Minutes Worked
7		7/24/2023	7/25/2023	7/26/2023	7/27/2023	7/28/2023	7/29/2023	7/30/2023
8	Project 1	30	60	45	10	120			265
9	Project 2
10	Project 3
11	Project 4
12	Project 5
13	Project 6
14	Project 7
15	Project 8
16	Project 9	60	60	60	60	60			300
17
18								Grand Total H:M for the week	4:25
19									0
20									0
21									0
22									0
23									0
24									0
25									0
26									5:00
7-24-2023

Cell Formulas
Range	Formula
I8:I16	I8	=SUM(B8:H8)
I18:I26	I18	=I8:I16/24/60
Dynamic array formulas.

 

[Bosquedeguate]

Try this formula in your total minutes cell
=INT(i18/60)&":"&TEXT(MOD(i18,60),"00")

 

[Fluff]

That is going to return text & not a number, Also as I18 is the Total minutes cell it ill give a circular reference.

@searchingforhelp did you try what I suggested in post#5?

 

[Bosquedeguate]

If the result should be displayed as hh:mm use:
=INT(SUM(sum range)/60)&":"&TEXT(MOD(SUM(sum range),60),"00")

If you want the sum formatted as a number (hh.mm) try:
=INT(SUM(sum range)/60) + MOD(sum range,60)/100 and
format the cell with two (2) decimal places

 

[Fluff]

Or just use the far simpler formula I suggested.