Round up time

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

you are not rounding up but rounding down

this formula will do the thrick to full hour
In cell F3:

Formula:

Please Login or Register  to view this content.

use 0,5 or 0,25 instead of 1 for rounding to the half hours or quarter of an hour.

What if the rounding down for certain employee like the SAMPLE 2 in the attachment ?

As there is employee that finished work at 6 and the overtime already 1 hour
Meanwhile, got certain employee that need to work until 6.30 then can count as 1 hour overtime
There will be no half an hour overtime for all employee.

Like I said if you change 1/24 to 0,5/24 it will round to half an hour.

If you want something else you will have to make a clearer example of how to determine if the shift supposed to end on a whole hour or on half an hour. I cannot see a way of knowing that now..

Try:

1. 18:00 - 18:59 -> 18:00

=FLOOR(E3,1/24)

2. 19:30 - 20:29 -> 19:30

=FLOOR(E32-1/48,1/24)+1/48