total weekly shift hours as decimal number

[ajm]

folks, this should be simple enough but has got me stumped. working out shift hours as decimal numbers. eg 19:30 (19 hours, 30 minutes) is 19.5. Using MOD formula gives the correct answer (MOD(Q6,1)*24) where Q6 holds the number of hours worked for the week. However, for total shift hours greater than 24, it returns the balance of hours not including the days. so, 43:00 hours returns 19.00 (instead of 43.00) and 48:30 hours, returns 0.50 instead of 48.50. what should i be doing?

 

[Phuoc]

Maybe like this

Q6*24

 

[ajm]

Maybe like this

Q6*24

tried that in my current format and it didn't work. but, by changing the divisor from 1 to 60 worked. so, now my formula is MOD(Q6*24,60). interestingly, MOD(Q6,60)*24 also works.

thanks for helping out

 

[Dave Patton]

B4 is custom formatted [h]:mm

Date and Time 3.xlsm
	A	B	C
1
2		19:30	19.5
3		20:30	20.5
4		40:00	40.0
1f

Cell Formulas
Range	Formula
C2:C3	C2	=B2*24
B4:C4	B4	=SUM(B2:B3)

 

[ajm]

Dave, so much simpler than mine. I must have messed up the particular custom format to begin with.

 

[Dave Patton]

Thanks for the feedback.

 

[ajm]

that's better. thanks Dave. as i said above I must have muffed the cell formatting somehow to begin with. my last answer, prior to this one, does the job still. this is just easier.