Formula wanted

[CHML]

Hey there..
Attached is a report of the sessions. Some of them - in this case, 3 were 45-minute sessions and the other 3 were 60-minute sessions. Now, I need a total of HOURS. I need to break up the total amount of sessions in BA26, 3 of them are 45 min, and the other 3 60 min. In the formula I tried, I was not successful as this number doesnt make sense.

 

[awoohaw]

Which items are 45 min and which are 60 min?

Can you do the forum a big favor please, and post your data using the xl2bb add in(Link below). If you cannot, please post the data as a table.

Thanks in advance.

 

[AhoyNC]

See if this helps. In Excel 45 mins = 0.03125. 1 day = 24 in Excel, so 1 hour = 1/24.
See example below.

Scaling Numbers from list.xlsx
	A	B	C	D	E
1	1 day =	24
2	1 Hour = 1/24 =	0.041666667
3	45 mins is 3/4 or .75 of an hour =	0.03125
4
5	So 45 mins is equal to 0.03125 in Excel
6	So take the value in your cell BA26 and multiply by 0.03125	0.1875
7	Then multiply by 24 to get hours	4.5	hours (4 hrs 30 mins)
8
Sheet3

Cell Formulas
Range	Formula
B2	B2	=1/24
B3	B3	=B2*0.75
B6	B6	=6*B3
B7	B7	=24*B6

 

[CHML]

Thanks AhoyNC, that would be the case if all six sessions were 45-minute sessions, however, in my case, three of them were 45-minute sessions, and the other three were 60-minute sessions, so what would be the formula in this case?

Thanks awoohaw, for providing the links, so far I haven't had a chance to explore the mini-sheet, but I would definitely benefit from it.

 

[AhoyNC]

See formula in A10.
It would be (3 x 0.03125 + 3 x 0.041666667) x 24

Scaling Numbers from list.xlsx
	A	B	C	D
1	1 day =	24
2	1 Hour = 1/24 =	0.041666667
3	45 mins is 3/4 or .75 of an hour =	0.03125
4
5	So 45 mins is equal to 0.03125 in Excel
6	So take the value in your cell BA26 and multiply by 0.03125	0.1875
7	Then multiply by 24 to get hours	4.5	hours (4 hrs 30 mins)
8
9	If 3 sessions are 45 mins and 3 are 1 hour
10	5.25	hours (5hrs 15 mins)
Sheet3

Cell Formulas
Range	Formula
B2	B2	=1/24
B3	B3	=B2*0.75
B6	B6	=6*B3
B7	B7	=24*B6
A10	A10	=(3*0.03125+3*0.041666667)*24

 

[CHML]

Thank you.

 

[AhoyNC]

You're welcome. Thanks for the feedback.
Another option would be to use the TIME function in Excel.

Book1
	A
1	With TIME function:
2	=(3*TIME(0,45,0)+3*TIME(1,0,0))*24
3	5.25
Sheet3

Cell Formulas
Range	Formula
A2	A2	=FORMULATEXT(A3)
A3	A3	=(3*TIME(0,45,0)+3*TIME(1,0,0))*24