# MRound and Timesheets

#### PDSRach

##### New Member
Plese can someone help with a timesheet we are trying to create.

Currently we log the in/out times and that totals up. But what we want to do is to show the total in actual time and then in a further box round the hours worked to the nearest 15 mins. Currently the sheet is set up with a formula in the total box that rounds these up and then puts it in parts of the minute (I think) - the formula in this box is =IF(R16=0,"",ROUNDUP(HOUR((R17-R16)+(R19-R18)+(R21-R20))+MINUTE((R17-R16)+(R19-R18)+(R21-R20))/60,2))

 Day Sat Sun Mon Tue Wed Thu Fri Total In 8:25 9:00 9:00 9:00 Out 13:00 13:00 13:00 13:00 In 13:30 13:30 13:30 13:30 Out 17:15 17:00 17:00 16:30 In Out Total 8.34 7.50 7.50 7.00 30.34

<tbody>
</tbody><colgroup><col span="2"><col><col span="6"><col></colgroup>
But what i want this to do is the total to say the acutal time worked (wed = 8hrs 20 mins) and then in another box to round that 8hrs 20 mins down to 8.25 (or if it was 8hrs 25 mins to round it up to 8.5 - so to the nearest 15 mins)

Please can someone help with the two formulas for me?

Thanks

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
then for rounding up in row under total use, =ROUNDUP(SUM(Q22)*96,0)/96, (from Q23, Tuesday) (works on principle that there are 96 quarters in a day)
Make sure all cells formatted to HH:MM (hours and minutes)
Also I assumed your table started in M15.

Another way:

Code:
``````      --A-- -B-- -C-- -D-- --E-- --F-- --G-- --H-- --I--
1    Day  Sat  Sun  Mon   Tue   Wed   Thu   Fri  Total
2   In                    8:25  9:00  9:00  9:00
3   Out                  13:00 13:00 13:00 13:00
4   In                   13:30 13:30 13:30 13:30
5   Out                  17:15 17:00 17:00 16:30
6   In
7   Out
8   Total 0.00 0.00 0.00  8.25  7.50  7.50  7.00 30.25``````

In B8 and copy across,

=MROUND(SUMPRODUCT(B2:B7 * {-1;1;-1;1;-1;1}), "0:15")*24

In I8, =SUM(B8:H8)

Replies
2
Views
119
Replies
9
Views
188
Replies
3
Views
127
Replies
9
Views
172
Replies
4
Views
411

1,203,187
Messages
6,053,992
Members
444,696
Latest member
VASUCH

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back