MROUND and time cards

[sideabeef]

=MROUND(IF((OR(B13="",D13="")),0,IF((D13<B13),((D13-B13)*24)+24,(D13-B13)*24)-C13/60),1/60)
this is the line i use to calculate total hours at work.
if i have a starting time (B13) of 8:55 am and a break (C13) of 30 minutes and an end time (D13) of 5:33 pm
right now that calculation gives me a time out put of 8.13 hours but the problem is i get paid by the quarter hour.
how do i get the calculation to round the 8:55 am and the 5:33 pm to the nearest 15 minutes before it finds the difference and reports in (F13) a time of 8 hours rounded to the quarter hour.
when all said and done i need (B13) and (D13) to still show the time i entered but the total in (F13) needs to be the difference of a rounded (B13) and (D13) in a quarter hour format.

time-card-calculator_biweekly.xlsx
	A	B	C	D	E	F	G	H	I	J	K
11	Day of Week	Time In	Breaks (minutes)	Time Out		Total Hrs	Regular Hrs	Overtime Hrs	Sick Hrs	Holiday Hrs	Vacation Hrs
13	Mon 1-2	8:55 AM	30	5:33 PM		8.13	8.00	0.13
14	Tue 1-3	9:05 AM	30	5:45 PM		8.17	8.00	0.17
15	Wed 1-4	9:00 AM	45	6:30 PM		8.75	8.00	0.75
16	Thu 1-5	9:00 AM	45	6:30 PM		8.75	8.00	0.75
17	Fri 1-6	9:00 AM	40	4:45 PM		7.08	7.08	0.00
18	Sat 1-7	8:00 AM	0	10:00 AM		2.00	0.92	1.08
19	Sun 1-8					0.00	0.00	0.00
20						Total Hrs:	40.00	2.88	0.00	0.00	0.00
Biweekly

Cell Formulas
Range	Formula
F13:F19	F13	=MROUND(IF((OR(B13="",D13="")),0,IF((D13<B13),((D13-B13)*24)+24,(D13-B13)*24)-C13/60),1/60)
G13:G19	G13	=F13-H13
H13	H13	=MAX(IF($W$8,MAX(0,SUM(G12:G$12)+F13-$V$9),0),IF($W$6,IF(F13>$V$7,F13-$V$7,0),0))
H14:H19	H14	=MAX(IF($W$8,MAX(0,SUM(G$12:G13)+F14-$V$9),0),IF($W$6,IF(F14>$V$7,F14-$V$7,0),0))
A13	A13	=G8
A14:A19	A14	=A13+1
G20:K20	G20	=SUM(G13:G19)

Cells with Data Validation
Cell	Allow	Criteria
B13:B19	Time	between 12:00:00 AM and 11:59:59 PM
D13:D19	Time	between 12:00:00 AM and 11:59:59 PM

 

[gordsky]

Maybe something like this

=IF(OR(B3="",D3=""),0,IF(D3<B3,((MROUND((D3*24),0.25)-MROUND((B3*24),0.25)))+24,(((MROUND((D3*24),0.25)-MROUND((B3*24),0.25)))-(C3/60))))

 

[sideabeef]

Maybe something like this

=IF(OR(B3="",D3=""),0,IF(D3<B3,((MROUND((D3*24),0.25)-MROUND((B3*24),0.25)))+24,(((MROUND((D3*24),0.25)-MROUND((B3*24),0.25)))-(C3/60))))

i tryed this but it returned err:508 for all cells involved. i looked up the error code and it suggests that there may be more opening ( then closing ) or vise versa
Thank you very much for trying.

 

[gordsky]

i tryed this but it returned err:508 for all cells involved. i looked up the error code and it suggests that there may be more opening ( then closing ) or vise versa
Thank you very much for trying.

Same number of open/close.
Works for me when testing. Maybe you have something set differently

 

[sideabeef]

i think it may be that im useing open office at home and my excel sheet is on my laptop useing office 365. ill try it on there.

 

[sideabeef]

Maybe something like this

=IF(OR(B3="",D3=""),0,IF(D3<B3,((MROUND((D3*24),0.25)-MROUND((B3*24),0.25)))+24,(((MROUND((D3*24),0.25)-MROUND((B3*24),0.25)))-(C3/60))))

i found the error, the cells in your calculation were missing the 1, cell B13 not B3.
with that adjusted it works perfectly. Thank you so much.

 

[gordsky]

i found the error, the cells in your calculation were missing the 1, cell B13 not B3.
with that adjusted it works perfectly. Thank you so much.

glad i could help and apologies for poor cell reference

 

[dolphin_tlp]

i found the error, the cells in your calculation were missing the 1, cell B13 not B3.
with that adjusted it works perfectly. Thank you so much.

Maybe something like this

=IF(OR(B3="",D3=""),0,IF(D3<B3,((MROUND((D3*24),0.25)-MROUND((B3*24),0.25)))+24,(((MROUND((D3*24),0.25)-MROUND((B3*24),0.25)))-(C3/60))))

I have tried the formula you provided and i keep getting a #Value! error. I am wondering if it is because my new time clock is showing times as a 24 hour clock. Can you give me any assistance as to how i can correct my error?
I have tried to upload a mini sheet but the spreadsheet i am using won't seem to let me use the XL2BB add on.