MRound and Timesheets

PDSRach

New Member
Joined
Jul 2, 2012
Messages
1
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))

DaySatSunMonTueWedThuFriTotal
In 8:259:009:009:00
Out 13:0013:0013:0013:00
In 13:3013:3013:3013:30
Out 17:1517:0017:0016:30
In
Out
Total 8.347.507.507.0030.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.
Try changing your formula from your message to, =IF(Q16="",0,SUM(Q17-Q16)+SUM(Q19-Q18)+SUM(Q21-Q20))
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.
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top