Combind a Mod formula with Mround

[Robert Wyatt]

I have a formula to calculate my time but how would I combined it with Mround to have it round the time to nearest 1/4? I have an XL2BB to show y'all want I'm doing. Need to have the worked hours and the MROUND Total to be combined is there a way to do that?

Book1.xlsm
	B	C	D	E	F	G	H	I
2	Hours Scheduled To Work	Weekday	Shift Starts	Lunch Starts	Lunch Ends	Shift Ends	Worked Hours	MROUND TOTAL
3			9:55 AM			4:55 PM	7.00	7.00
4			2:55 PM	5:09 PM	6:08 PM	10:21 PM	6.45	6.50
5			6:00 PM			10:20 PM	4.33	4.25
6			8:00 AM			1:02 PM	5.03	5.00
7							0.00	0.00
8							0.00	0.00
9							0.00	0.00
10						Total Hrs:	22.82	22.75
Sheet1

Cell Formulas
Range	Formula
H3:H9	H3	=(MOD(G3-D3,1)-MOD(F3-E3,1))*24
I3:I9	I3	=MROUND(H3,1/4)
H10:I10	H10	=SUM(H3:H9)

 

[noahbrooks2]

By your table, to combine a formula for calculating time, =MROUND(A1, "0:15") ; and you want to incorporate it directly =MROUND((End_Time - Start_Time) * 24, 0.25) ; converts it to hours by multiplying by 24, and then rounds to the nearest 0.25 hours (15 minutes). custom formulas =ROUND(A1 / (1/96), 0) * (1/96)

 

[Robert Wyatt]

By your table, to combine a formula for calculating time, =MROUND(A1, "0:15") ; and you want to incorporate it directly =MROUND((End_Time - Start_Time) * 24, 0.25) ; converts it to hours by multiplying by 24, and then rounds to the nearest 0.25 hours (15 minutes). custom formulas =ROUND(A1 / (1/96), 0) * (1/96)

I would like to have in all in together so take out the MROUND Total and have in just under the worked hours Colum only.

 

[Fluff]

How about

=MROUND((MOD(G3-D3,1)-MOD(F3-E3,1))*24,1/4)

 

[Robert Wyatt]

How about

=MROUND((MOD(G3-D3,1)-MOD(F3-E3,1))*24,1/4)

Thank you so much it works great. I just never thought about putting the MROUND in first. I did try putting 1/4 at the end. thanks for the help.

 

[Fluff]

You're welcome & thanks for the feedback.